Find LAST occurrence of text 
Author Message
 Find LAST occurrence of text

Excel does not appear to have a function for finding the last
occurrence of a string in another string. (I'm splitting up full names
into first and last and wanted to make the split on the last space,
not the first. Is there any way (short of VBA ) to simulate a
'BackSearch' worksheet function?
Thanks!!!!


Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text


Quote:
>Excel does not appear to have a function for finding the
>last occurrence of a string in another string. (I'm
>splitting up full names into first and last and wanted to
>make the split on the last space, not the first. Is there
>any way (short of VBA ) to simulate a 'BackSearch'
>worksheet function?

For this precise case, try this array formula to return all
characters after the last space character in A1:

=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN
(A1))),1),0)-1)

For the more general case of finding all characters in A1
to the right of the last occurrence of the substring given
in A2, use this array formula:

=RIGHT(A1,MATCH(A2,MID(A1,LEN(A1)+1-ROW(INDIRECT(LEN(A2)
&":"&LEN(A1))),LEN(A2)),0)-1)

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful



Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text
If you're looking for where the last "f' is, for example, in cell A1's text,
use
=FIND(CHAR(12),SUBSTITUTE(A1,"f",CHAR(12),LEN(A1)-LEN(SUBSTITUTE(A1,"f",""))
))

Bob Umlas
Excel MVP



Quote:
> Excel does not appear to have a function for finding the last
> occurrence of a string in another string. (I'm splitting up full names
> into first and last and wanted to make the split on the last space,
> not the first. Is there any way (short of VBA ) to simulate a
> 'BackSearch' worksheet function?
> Thanks!!!!



Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text
Sorry for responding to myself. Best way: download and
install Laurent Longre's MOREFUNC.XLL add-in, which is
freely available at

http://longre.free.fr/english/

Follow the instructions provided with that package to
install it. Once you have, use the (nonarray) formula

=WMID(A8,WORDCOUNT(A8," "),1," ")

to pull the last word from a space-separated phrase. This
generalizes easily.

REINVENT THE WHEEL AS SELDOM AS POSSIBLE!

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful



Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text
Harlan,

=WMID(A8,-1,1) works also.

If the second argument is negative, it extracts the N last words, and
the default separator is " ".

Laurent

Harlan Grove a crit :

Quote:

> Sorry for responding to myself. Best way: download and
> install Laurent Longre's MOREFUNC.XLL add-in, which is
> freely available at

> http://longre.free.fr/english/

> Follow the instructions provided with that package to
> install it. Once you have, use the (nonarray) formula

> =WMID(A8,WORDCOUNT(A8," "),1," ")

> to pull the last word from a space-separated phrase. This
> generalizes easily.

> REINVENT THE WHEEL AS SELDOM AS POSSIBLE!

> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful



Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text
Harlan:

Thanks for the ideas. I'm afraid I couldn't get your formulas to work.
Kept getting #VALUE. And the .XLL file with the additional functions
is a great idea, but I need to distribute this spreadsheet to the
whole company.

The formula suggested by Bob Umlas below worked fine, though.

Thanks for your help.

- Bob

=FIND(CHAR(12),SUBSTITUTE(A1," ",CHAR(12),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))

On Wed, 15 Dec 1999 12:40:36 -0800, Harlan Grove

Quote:

>Sorry for responding to myself. Best way: download and
>install Laurent Longre's MOREFUNC.XLL add-in, which is
>freely available at

>http://longre.free.fr/english/

>Follow the instructions provided with that package to
>install it. Once you have, use the (nonarray) formula

>=WMID(A8,WORDCOUNT(A8," "),1," ")

>to pull the last word from a space-separated phrase. This
>generalizes easily.

>REINVENT THE WHEEL AS SELDOM AS POSSIBLE!

>* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful



Wed, 18 Jun 1902 08:00:00 GMT  
 Find LAST occurrence of text
Bob:

Bingo!  Just what I was looking for. Very interesting algorythm.

Thanks a lot.

- Bob



Quote:
>If you're looking for where the last "f' is, for example, in cell A1's text,
>use
>=FIND(CHAR(12),SUBSTITUTE(A1,"f",CHAR(12),LEN(A1)-LEN(SUBSTITUTE(A1,"f",""))
>))

>Bob Umlas
>Excel MVP



>> Excel does not appear to have a function for finding the last
>> occurrence of a string in another string. (I'm splitting up full names
>> into first and last and wanted to make the split on the last space,
>> not the first. Is there any way (short of VBA ) to simulate a
>> 'BackSearch' worksheet function?
>> Thanks!!!!



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Loop not finding last occurrence

2. Find in HTML message only finds first occurrence

3. Copying Last Occurrence

4. Find last text value in column

5. Find last text value in column

6. Find last text value in column

7. How to find the LAST instance of a character in a text string

8. Finding Number of Occurrences of String

9. find first occurrence in a column

10. Using InStr( [start,] strexpr1, strexpr2) to find occurrence of embedded TAB character in string


 
Powered by phpBB® Forum Software © phpBB Group