Extract middle int. from Last Name, Firlst Name Middle Int.

[DarrenF]

Hi there,

Looking for some help with a formula for removing middle initial from a cell with Last Name, First Name Middle Initial. Example:

Johns, Jimmy E

Also, some of the people that are in the list do not have a middle name.

Any help would be much appreciated!

Thanks!
Darren

 

[Michael M]

Try

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

 

[DarrenF]

Thank you Michael!

I oppologize, that worked for those that I gave the expample for. I forgot to metion that I also have some that have full middle names spelled out. Example:

Johns, Jimmy Edward

 

[Michael M]

Okay different approach

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

 

[azumi]

For have middle name or dont have it:

=LEFT(A1,SEARCH(",",A1,1)-1)&", "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99))

 

[DarrenF]

Perfect that worked! Thank you Both.

 

[István Hirsch]

Give this a try too:

=TRIM(MID(SUBSTITUTE(A1," ";REPT(" ",50)),1,100))