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

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

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

On Sun, 11 Jun 2006 05:24:02 -0700, PA <[email protected]> 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

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

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 <[email protected]> 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
>

On Sun, 11 Jun 2006 06:24:01 -0700, PA <[email protected]> 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