An easier way to get the last word from a string ?

[Steve]

Is there any way extract the last word from a string than this:

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

Where A1 contains the string and the above needs to be entered as an array
input (Ctrl+Enter).

Also is there a simple non-VBA way of converting an array to a string, or
even a string reverse function ?

(I've tried the above in Excel 2K3,XP & 2K)


Regards


Steve

 

[Peo Sjoblom]

One way

=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

entered normally

 

[Steve]

Nice & Simple, thanks.

One way

=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

entered normally

 

[Bernie Deitrick]

Peo,

That formula would be improved by using TRIM - it returns nothing when there
is a trailing space:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-SEARCH("^^",SUBSTITUTE(TRIM(A1),"
","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))))

HTH,
Bernie
MS Excel MVP