find last occurrence

[Guest]

How can I determine the starting position of the last occurrence of specific
text within a longer string of text? For example, the text "abc" occurs last
in the string "1abc2abc3abc" at position 10.

 

[Bernie Deitrick]

REMnLYN,

With the longer string of text in cell A1, and the shorter string in A2, the
array formula (entered with Ctrl-Shift-Enter) (This formula should be all on
one line, so watch the line wrapping):

=MAX((MID(A1,ROW(INDIRECT("A1:A" &
LEN(A1))),LEN(A2))=A2)*ROW(INDIRECT("A1:A" & LEN(A1))))

will return the value 10, based on your example.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Something like

=SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")))

Regards,

Peo Sjoblom

 

[Harlan Grove]

Bernie Deitrick wrote...

With the longer string of text in cell A1, and the shorter string in A2, the
array formula (entered with Ctrl-Shift-Enter) (This formula should be all on
one line, so watch the line wrapping):

=MAX((MID(A1,ROW(INDIRECT("A1:A" &
LEN(A1))),LEN(A2))=A2)*ROW(INDIRECT("A1:A" & LEN(A1))))

will return the value 10, based on your example.

....

Too long. Too redundant. More efficient,
=MATCH(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),LEN(A2))=A2))

 

[nathpai]

Hi all,
I thought I am the best in Excel and know all the functions. BUT now I
think I am a newbie after looking at the numourous posts in this NG.

I am using following formula in my sheet. However I could not
understand it at all.
May I request if someone can explain me how it works?
=SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")))


Also if I need the second last occurance or third last occurance, how
do I modify it.

Actually, I need to extrect the text between last two occurances of
"\". There may many alphanumaric cherecters (text) before and after
these occurances. The "\" itself appears many times in the same string.

I really appreciate the help by you people.

Nathpai.

 

[Peo Sjoblom]

This part

(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

will return how many substrings of "abc" there are in the string in A1,
thus if you want the last occurrence of "abc" the above formula will return
the last occurrence number
len(a1) will count the characters when substituted with null strings "" and
subtracted from
the total -LEN(SUBSTITUTE(A1,"abc","")) then divided with the numbers of
substring characters so if the string looks like

adfhabcvbfrabc

the formula will return 2 (6 (abc+abc) characters divided with 3 (abc)

now this part

SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",

will look for a unique string "^^^" could be anything that you are sure of
will not be
in the string in A1, it replaces the second substring "abc" with "^^^" and
thus can be
found, if there would be 4 substrings

(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

then the above will return 4 thus replacing the 4th occurrence of "abc" with
"^^^"
thus returning the number of characters to the first letter of "abc"

so if you want the second last occurrence subtract -1 from this part

(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

so

=SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))

will return second but last (make sure there are more than one occurrence or
it will return an error)

 

[nathpai]

Hi Peo Sjoblom,
Excellent explanation. Now I will be able to use the logic in future
also.

One more request:
Once we found out the seceond last and third last occurances, how do I
get the text between these two occurances?

I hope I am not asking too much.

Again thanks for your help.

Nathpai.

 

[Peo Sjoblom]

One way

=LEFT(MID(A1,SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))+LEN("abc"),255),SEARCH("abc",MID(A1,SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))+LEN("abc"),255))-1)

 

[Harlan Grove]

One way

=LEFT(MID(A1,SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",((LEN(A1)-LEN(SUBSTITUTE
(A1,"abc","")))/LEN("abc"))-1))+LEN("abc"),255),SEARCH("abc",MID(A1,SEARCH("
^^^",SUBSTITUTE(A1,"abc","^^^",((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("
abc"))-1))+LEN("abc"),255))-1)

 

[Harlan Grove]

One way

=LEFT(MID(A1,SEARCH("^^^",SUBSTITUTE(A1,"abc","^^^",
((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))
+LEN("abc"),255),SEARCH("abc",MID(A1,SEARCH("^^^",
SUBSTITUTE(A1,"abc","^^^",((LEN(A1)
-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))
+LEN("abc"),255))-1)

Text parsing in Excel is definitely not pretty!

Another way, which involves using the defined name seq referring to

=ROW(INDIRECT("1:1024"))

Also an array formula.

=MID(A1,LARGE(IF(MID(A1,seq,LEN("abc"))="abc",seq),3)+LEN("abc"),
SUMPRODUCT(LARGE(IF(MID(A1,seq,LEN("abc"))="abc",seq),{2,3}),
{1,-1})-LEN("abc"))

However, udfs encapsulating calls to Windows Scripting Host regular
expression objects would be much, much better for this. Using the Subst udf
from

http://groups-beta.google.com/group...programming/msg/1d9ae07c970566de?dmode=source

(or http://makeashorterlink.com/?S512525CA )

the same result is given by

=subst(A1,".*abc(.*)(abc.*){2}$","$1")