Formula to remove initials from a name

[FlowersinExcel]

Hi all,
I've looked at other threads asking how to remove a middle initial from a name, and tried those formulas with no luck, so I want to ask here. How to I remove the initials from this list of first names?

Full Name	Last Name	First Name	First name that I want to appear
These are hard-coded	=LEFT(A3,FIND(", ",A3,1)-1)	=RIGHT(A3,LEN(A3)-FIND(", ",A3,1)-1)	?????
Kim, Ki Tae Rick	Kim	Ki Tae Rick	Ki Tae Rick
Kim, Sa Kee	Kim	Sa Kee	Sa Kee
Rodriguez Vasquez, Vanessa S	Rodriguez Vasquez	Vanessa S	Vanessa
Amador, Omar M	Amador	Omar M	Omar
Cruz, Ai	Cruz	Ai	Ai

Thanks so much!

 

[shinigamilight]

=IFERROR(IF(LEN(MID(TRIM(MID(A3,SEARCH(",",A3)+1,95)),1+SEARCH(" ",TRIM(MID(A3,SEARCH(",",A3)+1,95))),95))=1,MID(TRIM(MID(A3,SEARCH(",",A3)+1,95)),1,SEARCH(" ",TRIM(MID(A3,SEARCH(",",A3)+1,95)))-1),TRIM(MID(A3,SEARCH(",",A3)+1,95))),TRIM(MID(A3,SEARCH(",",A3)+1,95)))

 

[awoohaw]

It just looks to see if the penultimate character is a space.

But the formula deleted the M for Omar, does your formula look for second names with more than one letter as well?

 

[Fluff]

But the formula deleted the M for Omar

That's what it's meant to do.

 

[Peter_SSs]

So let's say no, no double initials! Does that make it easier?

Thanks .. and 'yes'

Another option for First Name

23 03 04.xlsm
	A	B	C
1	Full Name	Last Name	First Name
2	Kim, Ki Tae Rick	Kim	Ki Tae Rick
3	Kim, Sa Kee	Kim	Sa Kee
4	Rodriguez Vasquez, Vanessa S	Rodriguez Vasquez	Vanessa
5	Amador, Omar M	Amador	Omar
6	Cruz, Ai	Cruz	Ai
Names

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,FIND(",",A2)-1)
C2:C6	C2	=SUBSTITUTE(IF(LEFT(RIGHT(A2,2),1)=" ",LEFT(A2,LEN(A2)-2),A2),B2&", ","")

 

[FlowersinExcel]

How about

Fluff.xlsm
	A	B	C
1	Full Name	Last Name	First Name
2	Kim, Ki Tae Rick	Kim	Ki Tae Rick
3	Kim, Sa Kee	Kim	Sa Kee
4	Rodriguez Vasquez, Vanessa S	Rodriguez Vasquez	Vanessa
5	Amador, Omar M	Amador	Omar
6	Cruz, Ai	Cruz	Ai
Main

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,FIND(",",A2)-1)
C2:C6	C2	=IF(MID(A2,LEN(A2)-1,1)=" ",REPLACE(LEFT(A2,LEN(A2)-2),1,LEN(B2)+2,""),REPLACE(A2,1,LEN(B2)+2,""))

This worked perfectly, thank you!

 

[FlowersinExcel]

Thanks .. and 'yes'

Another option for First Name

23 03 04.xlsm
	A	B	C
1	Full Name	Last Name	First Name
2	Kim, Ki Tae Rick	Kim	Ki Tae Rick
3	Kim, Sa Kee	Kim	Sa Kee
4	Rodriguez Vasquez, Vanessa S	Rodriguez Vasquez	Vanessa
5	Amador, Omar M	Amador	Omar
6	Cruz, Ai	Cruz	Ai
Names

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,FIND(",",A2)-1)
C2:C6	C2	=SUBSTITUTE(IF(LEFT(RIGHT(A2,2),1)=" ",LEFT(A2,LEN(A2)-2),A2),B2&", ","")

Peter, this worked too, thank you!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.