Finding a Substring?

  • Thread starter Thread starter Guest
  • Start date Start date
Try something like this:

With
A1: abcaaaaaaaaaadddddddabczzzzzzzzzzzzzabcyyyyy

This formul returns the starting position of the last occurrence of "abc"
=SEARCH("|",SUBSTITUTE(A1,"abc","|",(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
I'm in the mood today to write my own functions. try this

Function FindLast(ShortString, LongString) As Integer

Shortlength = Len(ShortString)
LongLength = Len(LongString)

FindLast = 0
If (Shortlength <= LongLength) Then

StringPosition = LongLength - Shortlength + 1

For i = StringPosition To 1 Step -1

If (StrComp(ShortString, Left(Mid(LongString, i, Shortlength),
Shortlength)) = 0) Then
FindLast = i
Exit For
End If


Next i

End If

End Function
 
Thank you very much.


--
jake


Joel said:
I'm in the mood today to write my own functions. try this

Function FindLast(ShortString, LongString) As Integer

Shortlength = Len(ShortString)
LongLength = Len(LongString)

FindLast = 0
If (Shortlength <= LongLength) Then

StringPosition = LongLength - Shortlength + 1

For i = StringPosition To 1 Step -1

If (StrComp(ShortString, Left(Mid(LongString, i, Shortlength),
Shortlength)) = 0) Then
FindLast = i
Exit For
End If


Next i

End If

End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I handle error conditions with the FIND command? 6
Counting cells that contain a substring 3
search text for substring and change text colour of substring 5
data filtering via vlookup or index/match/find or search withwildcard 10
sum wrt substrings! 8
counting occurences of a substring 1
Replace First Occurrance of a substring 5
Subquery with Select Substring 1

Back
Top