Finding a Substring?
 

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

Finding a Substring?
 

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


"Jakobshavn Isbrae" wrote:

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

Finding a Substring?
 

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

"Jakobshavn Isbrae" wrote:

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

Finding a Substring?
 

Thank you.

This is something I can use.
--
jake


"Ron Coderre" wrote:

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


"Jakobshavn Isbrae" wrote:

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

Finding a Substring?
 

Thank you very much.


--
jake


"Joel" wrote:

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

"Jakobshavn Isbrae" wrote:

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