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

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: An easier way to get the last word from a string ? by Peo

Peo
Wed Apr 27 08:59:38 CDT 2005

One way

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

entered normally

--
Regards,

Peo Sjoblom


"Steve" <news_svaardt@hotmail_NOSPAM_.com> wrote in message
news:OyNz$oySFHA.3312@TK2MSFTNGP12.phx.gbl...
>
> 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
>
>
>

Top

Re: An easier way to get the last word from a string ? by Steve

Steve
Wed Apr 27 09:51:53 CDT 2005


Nice & Simple, thanks.




"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:OR2egFzSFHA.2560@TK2MSFTNGP09.phx.gbl...
> One way
>
> =RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
> ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
>
> entered normally
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "Steve" <news_svaardt@hotmail_NOSPAM_.com> wrote in message
> news:OyNz$oySFHA.3312@TK2MSFTNGP12.phx.gbl...
>>
>> 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
>>
>>
>>
>


Top

Re: An easier way to get the last word from a string ? by Bernie

Bernie
Wed Apr 27 09:53:40 CDT 2005

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


"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:OR2egFzSFHA.2560@TK2MSFTNGP09.phx.gbl...
> One way
>
> =RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
> ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
>
> entered normally
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "Steve" <news_svaardt@hotmail_NOSPAM_.com> wrote in message
> news:OyNz$oySFHA.3312@TK2MSFTNGP12.phx.gbl...
> >
> > 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
> >
> >
> >
>


Top