PDA

View Full Version : Finding a string within a string multiple times



jdilts
11-08-2012, 12:10 PM
The title is kind of confusing.
What I mean is let's say I have a string in a cell
"Want food? food please."

I search for food.
InStr(1,"Want food? food please","food")

How do I get both positions of where both "food"s are located? Instead of just one.(I believe InStr only gives me the location of the first "food" and not the second one. Correct me if I'm wrong.).

Thanks!

Bob Phillips
11-08-2012, 03:18 PM
Sub FindAll()
Dim mText As String
Dim mSearch As String
Dim mOut As String
Dim pos As Long

mText = "Want food? food please"
mSearch = "food"
pos = 1
Do While pos > 0

pos = InStr(pos, mText, mSearch)
If pos > 0 Then

mOut = mOut & pos & ","
pos = pos + 1
End If
Loop

If mOut <> "" Then

MsgBox Left$(mOut, Len(mOut) - 1)
End If
End Sub

snb
11-08-2012, 03:36 PM
Sub M_snb()
sn = Split("Want food? food please", "food")

For j = 0 To UBound(sn) - 1
x = x + Len(sn(j)) + IIf(j > 0, Len("food"), 0)
y = y & ", " & x + 1
Next

MsgBox Mid(y, 3)
End Sub

Teeroy
11-08-2012, 07:44 PM
Not as concise, but as a general Function to output an array

Function MatchAll(SWhole As String, sSearch As String)
Dim posn(), vParts
Dim temp As Integer, i As Integer

vParts = Split(SWhole, sSearch)
If UBound(vParts) > LBound(vParts) Then
ReDim posn(LBound(vParts) To UBound(vParts) - 1)
For i = LBound(vParts) To UBound(vParts) - 1
temp = Len(vParts(i)) + temp
posn(i) = temp + 1
temp = temp + Len(sSearch)
Next i
Else
ReDim posn(0)
posn(0) = 0
End If
MatchAll = posn
End Function

jdilts
11-09-2012, 06:56 AM
Wow! Thank you everyone.