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

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

"Elkar" wrote in message
...
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.