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

Hi Kathy,

To remove the middle initial from the end of the name, you can use the

Code:

LEFT

and

Code:

FIND

functions in Excel. Here are the steps:

1. Assuming the name is in cell A1, create a new column next to it by right-clicking on the column header and selecting "Insert."
2. In the new column, enter the following formula in the first row:
   Code:

   =LEFT(A1,FIND(",",A1)-1)

3. Press Enter to apply the formula. This will extract the last name and first name from the original cell, up to the comma.
4. Copy the formula down to the rest of the rows in the column by clicking on the bottom right corner of the cell and dragging it down.
5. The new column should now contain the last name and first name without the middle initial.

If there are cases where there is a suffix like "Jr." at the end of the name, you can modify the formula to remove it as well. For example, if the suffix is always two characters, you can use the following formula instead:

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)

"kswan" wrote in message
...
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

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

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

"Bob Phillips" wrote:

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)

"kswan" wrote in message
...
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

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.

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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.

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

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)

"kswan" wrote in message
...
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

"Bob Phillips" wrote:

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)

"kswan" wrote in message
...
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

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" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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.

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

Thanks Rick

That worked great.
Kathy

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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.

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