Formula to find a space in a cell and bring back the word after the space.

[jswinney1996]

I have cells that have names, I want to write a formula to from the right and find the space and return the last name only.

Example Cell A2 has John Doe I want a formula in Cell B2 that brings back just Doe.

 

[Scott T]

This should work if the names are all first and last only, no middle name or initial.

=TRIM(MID(A2,SEARCH(" ",A2)+1,9999))

 

[jswinney1996]

Thank You

 

[Scott T]

Glad to help

 

[Rick Rothstein]

This should work if the names are all first and last only, no middle name or initial.

=TRIM(MID(A2,SEARCH(" ",A2)+1,9999))

And if there could be middle names or initials, this formula will find the name (or word for that matter) after the last space no matter how many name parts (or words) are in the cell...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))