Parse from the Right
 

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

Parse from the Right
 

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.

"PA" wrote:

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

Parse from the Right
 

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).

"PA" wrote:

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

Parse from the Right
 

On Sun, 11 Jun 2006 05:24:02 -0700, PA wrote:

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

Parse from the Right
 

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.


"bpeltzer" wrote:

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).

"PA" wrote:

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

Parse from the Right
 

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

"Ron Rosenfeld" wrote:

On Sun, 11 Jun 2006 05:24:02 -0700, PA wrote:

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

Parse from the Right
 

On Sun, 11 Jun 2006 06:24:01 -0700, PA wrote:

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