Finding a Substring?

[Guest]

How can I find the last occurrence of a substring with a larger string?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you.

This is something I can use.

 

[Guest]

Thank you very much.


--
jake

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