I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

This checks to see if a middle initial is present, and leaves it out if true.

=IF(ISERROR(LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vicky" wrote:

I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

This may work, depending on consistency of a blank after the comma
=IF(MID(A3,LEN(A3)-3,1)=",",LEFT(A3,LEN(A3)-4),A3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vicky" wrote in message
...
I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a
lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

Sorry, there is no comma between the FIRST and MI. Should I change your
commas to a space?

Name appears like this
Smith, Mary A

Thanks!



"Luke M" wrote:

This checks to see if a middle initial is present, and leaves it out if true.

=IF(ISERROR(LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vicky" wrote:

I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

that seemed to work

"Vicky" wrote:

Sorry, there is no comma between the FIRST and MI. Should I change your
commas to a space?

Name appears like this
Smith, Mary A

Thanks!



"Luke M" wrote:

This checks to see if a middle initial is present, and leaves it out if true.

=IF(ISERROR(LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vicky" wrote:

I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

Based on last example with NO period after the initial

=IF(MID(A10,LEN(A10)-1,1)=" ",LEFT(A10,LEN(A10)-1),A10)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
This may work, depending on consistency of a blank after the comma
=IF(MID(A3,LEN(A3)-3,1)=",",LEFT(A3,LEN(A3)-4),A3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vicky" wrote in message
...
I have a list of names, listed as LAST, FIRST, MI. The Middle initial is
optional, and I would like to remove it for the purpose of creating a
lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.

Try this:

=IF(LEFT(RIGHT(A1,2))=" ",LEFT(A1,LEN(A1)-2),A1)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Vicky" wrote in message
...
Sorry, there is no comma between the FIRST and MI. Should I change your
commas to a space?

Name appears like this
Smith, Mary A

Thanks!



"Luke M" wrote:

This checks to see if a middle initial is present, and leaves it out if
true.

=IF(ISERROR(LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2)+1)-1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vicky" wrote:

I have a list of names, listed as LAST, FIRST, MI. The Middle initial
is
optional, and I would like to remove it for the purpose of creating a
lookup
from another table.

I have been doing Text to Columns and then CONCATENATE for the first 2
columns.
I was wondering if there is a way to do this with a function. Thanks in
advance.