Remove initial from end of name

[kswan]

I have browsed the questions to remove the middle initial from the name cell
and see the response if the middle initial is in the middle of the name in
one cell.

The problem I have is the middle initial is at the end of the names.

eg Smith, John J.
I need to have Smith, John in one cell and remove the J. In some cases there
may be a J. Jr.

Any help would be appreciated
Kathy

 

[Bob Phillips]

If it is always that format

=LEFT(A11,FIND(" ",A11,FIND(",",A11)+2))

=SUBSTITUTE(A11,B11,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I have browsed the questions to remove the middle initial from the name

cell
and see the response if the middle initial is in the middle of the name in
one cell.

The problem I have is the middle initial is at the end of the names.

eg Smith, John J.
I need to have Smith, John in one cell and remove the J. In some cases
there
may be a J. Jr.

This will do what you asked

=LEFT(A19,FIND(" ",A19,1+FIND(" ",A19))-1)

but there are many name combinations that will fool it. For example, I have
a friend whose first name is Mary Anne (a two word name with a space in the
middle). Also, I once worked with a person named Frank Della Rossa... Della
Rossa (two words with a space in the middle) was his last name. When it
comes to names, there really isn't any hard and fast rules that will work
for all of them.

Rick

 

[kswan]

Thanks Bob,

It works for all cases where there is a middle initial at the end of the
name but in some cases there is no middle initial just the name.

What happens when I apply the formula below I get #value for those people
who have no middle initial

Any advise.
Thanks again

 

[Rick Rothstein \(MVP - VB\)]

I have browsed the questions to remove the middle initial from the name

This will do what you asked

=LEFT(A19,FIND(" ",A19,1+FIND(" ",A19))-1)

but there are many name combinations that will fool it. For example, I
have a friend whose first name is Mary Anne (a two word name with a space
in the middle). Also, I once worked with a person named Frank Della
Rossa... Della Rossa (two words with a space in the middle) was his last
name. When it comes to names, there really isn't any hard and fast rules
that will work for all of them.

Bob had a good idea picking up on that comma... here is my formula modified
to take that into account...

=LEFT(A19,FIND(" ",A19,2+FIND(", ",A19))-1)

This would solve the double last name issue, but you are still left with the
double first name problem.

Rick

 

[kswan]

Thanks to both of you.

Do I need to turn this into an If statement to account for those that do not
have a middle initial at the end of their name.

 

[Bob Phillips]

Use this for the first then

=IF(ISNUMBER(FIND(" ",A11,FIND(",",A11)+2)),LEFT(A11,FIND("
",A11,FIND(",",A11)+2)),A11)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Yes, you would need an IF statement then. Here is my formula modified for
this...

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,LEFT(A1,FIND(" ",A19,2+FIND(",
",A1))-1),A1)

Rick

 

[kswan]

Thanks Rick

That worked great.
Kathy

Yes, you would need an IF statement then. Here is my formula modified for
this...

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,LEFT(A1,FIND(" ",A19,2+FIND(",
",A1))-1),A1)

Rick