Formula for stripping text

[Vicky]

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.

 

[Don Guillett]

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)

 

[Vicky]

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!

 

[Vicky]

that seemed to work

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!

 

[Don Guillett]

Based on last example with NO period after the initial

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

 

[RagDyeR]

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!
=====================================================


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!