You could try this:

=TRIM(SUBSTITUTE(full_name,last_name,""))

Substituting the cell reference for full_name and the formula you choose for
last_name.


Katie wrote:
Thank you guys for the help isolating the last name but is there a way to
remove it in the original cell? So right now we have:

Mr John Smith / Smith

Is there a way to make it:

Mr John / Smith

?? Thank you !!!

"Katie" wrote:

Hi! I am trying to separate suffix/first/last name into different column.
Currently I work with different formats:
Mr John Smith
Mr and Mrs. Smith
Mr Smith
Dr and Ms Smith
Mr John and Mrs Jane Smith
Is there a way to do this without having going to Text to Column- Delimited-
and separate everything by a space and then putting it all back together?
Even something to just separate the last name or the last word in a column
would be very helpful. Thank you!!!