Parse from the Right

[Guest]

One of our Administrative Assistants set up a worksheet with Name in one
cell. Unfortunately, she has some with first and last name only, and some
include a middle name.
I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for
those cells that contain only first and last name, but, if a cell contains a
middle name, this formula yields the middle and last name because it starts
from the left. I need only the last name.
I believe I need a formula that starts from the right, finds the first space
from the right, and then returns all characters to the left of that space.
I cant figure out how to do that.
Any suggestions will be appreciated.
Paul

 

[Guest]

Sorry, to correct the end of my post, the line "I believe I need a formula
that starts from the right, finds the first space from the right, and then
returns all characters to the left of that space" should be to the RIGHT of
that space.
In summary, I need to find the first space from the right, and then return
all the characters to RIGHT of that space.
Sorry, dumb mistake.

 

[Guest]

If you're going to do this with formulas, I'd use =RIGHT(A1,LEN(A1)-FIND("
",A1)) for the first name, last name case and =RIGHT(A1,LEN(A1)-(FIND("
",A1,FIND(" ",A1)+1))) where you have first, middle, last. Rather than
parsing from the right, I'm just looking for the second space character.
If you would consider NOT using formulas, check Data > Text To Columns.
Excel can do the splits for you, based on your choice of delimiters (and can
also handle repeated delimiters, such as two spaces where you're only
expecting one).

 

[Ron Rosenfeld]

One of our Administrative Assistants set up a worksheet with Name in one
cell. Unfortunately, she has some with first and last name only, and some
include a middle name.
I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for
those cells that contain only first and last name, but, if a cell contains a
middle name, this formula yields the middle and last name because it starts
from the left. I need only the last name.
I believe I need a formula that starts from the right, finds the first space
from the right, and then returns all characters to the left of that space.
I cant figure out how to do that.
Any suggestions will be appreciated.
Paul

This formula will give you the last word in the string, so long as there are at
least two words.

=MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1),
LEN(A10)-LEN(SUBSTITUTE(A10," ",""))))+1,255)


--ron

 

[Guest]

Two possible problems with your solutions,
Unless I am doing something incorrect with the Text to Colums, middle names
get mixed up with last names, and the last names of those with middle names
get pushed out an additional column to the right.
The formula solution you propose works, except that I need to examine the
data in each row and then put in the appropriate formula.

 

[Guest]

Thanks Ron, that does it for us. It even works if there are four name.

 

[Ron Rosenfeld]

Thanks Ron, that does it for us. It even works if there are four name.

You're welcome. Thanks for the feedback. Yes, it will always return the
characters after the last <space>, no matter how many words/names there are.
It will give an error if there is one or zero words. Also, if there are any
trailing spaces, the formula will return a <blank>.

In order to eliminate both of those problems, one could change the formula to:

=IF(ISERR(FIND(" ",TRIM(A1))),"",MID(TRIM(A1),FIND(
CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(
TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,255))


--ron