Right Function

[Irina]

Hi guys,

I have to get the last word from text cell.

For example,

Mr. and Mrs. Smith

I need to get Smith. The number of words is changeable.

Thanks a lot in advance,

Irina.

[Elkar]

Try this:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)

This basically replaces all spaces in your text with the ~ tilde character,
except the last one. It then looks for the one remaining space and returns
all text to the right of that. Note that if your text already contains
tildes, then you should use a different symbol.

HTH,
Elkar


"Irina" wrote:

>
> Hi guys,
>
> I have to get the last word from text cell.
>
> For example,
>
> Mr. and Mrs. Smith
>
> I need to get Smith. The number of words is changeable.
>
> Thanks a lot in advance,
>
> Irina.
>
>
> --
> Irina
> ------------------------------------------------------------------------
> Irina's Profile: http://www.excelforum.com/member.php...fo&userid=8685
> View this thread: http://www.excelforum.com/showthread...hreadid=501197
>
>

[Bob Phillips]

"Elkar" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
> ",""))))+1,1024)
>
> This basically replaces all spaces in your text with the ~ tilde
character,
> except the last one.

Actually, it only replaces the last space with tilde.