string matching

  • Thread starter Thread starter Alex Lee
  • Start date Start date
A

Alex Lee

Dear all:
I have newbie question on how to match strings with excel macro.
What I want is to match a word against different elements of an array
and do something with it if matches.

Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./

----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if

Next
----------------------------------------------


Unfortunenly the code above does not work. Any help?

thanks. alex
 
Hi Alex,

Try something like:

'=============>>
Public Sub Tester()
Dim x As Long
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If InStr(1, arr(x), "apple", vbTextCompare) Then
MsgBox arr(x)
End If

Next x
End Sub
'<<=============
 
Hi Alex,

I should, however, have added that, unless you have
declared Option Base as 1 at the head of your module,
your instruction:
ReDim arr(3)
Click to expand...

is equivalent to:

ReDim arr(0 to 3)
 
I have newbie question on how to match strings with excel macro.
What I want is to match a word against different elements of an array
and do something with it if matches.

Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./

----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if

Next
----------------------------------------------
Click to expand...

You could use the InStr method Norman posted, or one that looks almost like
a regular expression...

For x = 1 To 3
If arr(x) Like "*[aA]pple*" Then
MsgBox arr(x)
End If
Next x

where the [aA] allows "apple" to start with either a lower or upper case
"A".

Another possibility is to use the Filter function to create an array of just
elements from the original array that contain the search word (apple, in
your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr
Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub


Rick
 
Typed too fast... see inline correction.
Another possibility is to use the Filter function to create an array of
just elements from the original array that contain the search word (apple,
in your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr
Click to expand...

The above should be... Dim SubArr() As String
Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub
Click to expand...

Rick
 
Hi Norman:
that totally worked! thanks. I've searched google for an hr but could
not find a solution.

alex
 
Rick,
thanks for your help. You are right about this method being more
regular expression like. Actually I prefer this method because of the
similiartiy to regex.

alex

I have newbie question on how to match strings with excel macro.
What I want is to match a word against different elements of an array
and do something with it if matches.
Click to expand...
Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./
Click to expand...
----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"
Click to expand...
For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if
Click to expand...
Next
----------------------------------------------
Click to expand...

You could use the InStr method Norman posted, or one that looks almost like
a regular expression...

For x = 1 To 3
If arr(x) Like "*[aA]pple*" Then
MsgBox arr(x)
End If
Next x

where the [aA] allows "apple" to start with either a lower or upper case
"A".

Another possibility is to use the Filter function to create an array of just
elements from the original array that contain the search word (apple, in
your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr
Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub

Rick- Hide quoted text -

- Show quoted text -
Click to expand...
 
thanks for your help. You are right about this method being more
regular expression like. Actually I prefer this method because of the
similiartiy to regex.
Click to expand...

Just so you know, though, the InStr test is "faster" than the Like operator
comparison; but you wouldn't see the difference unless you were running a
huge loop. You might want to look again at the Filter function as that is
remarkably flexible in finding sub-strings within String arrays.

Rick
 
You might want to look again at the Filter function as that is remarkably
flexible in finding sub-strings within String arrays.
Click to expand...

And the reason why is that you don't have to examine each individual array
element in a loop looking for matching substrings... true, Filter must do
that behind the scenes, but it is so much quicker in doing so than a VBA
loop can ever be.

Rick
 
Back
Top