Need some help with name replacement type formulas

[havocdragon]

I am having some difficulties getting this to work, but here is what I have;

for arguments sake, we will say the first name is John, last name Doe,
middle initial X. I have a list of 200 names that look like this, that do me
no good in this format.

Doe, John
Doe, John X
Doe, X John

Now I can get a formula that resolves the first one:
=CONCATENATE(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"
","*",LEN(H2)-LEN(SUBSTITUTE(H2," ","")))))&" "&LEFT(H2,FIND(" ",H2)-1))

However as you can probably tell, this falls apart on the second and third
names...I need a formula that will fix both of them...and I cant quite seem
to get it right. I know its gotta be a simple factor of an IF statement, I
just can't get the math right as I am not very good with these types of
functions =(

 

[CLR]

Have you considered using Data > TextToColumns > using Comma as the
delimiter.......then you can do it again on the second column using space as
the delimiter......then the concatenation to re-construct the names should be
much easier......

Vaya con Dios,
Chuck, CABGx3

 

[havocdragon]

Yes that worked for the most part, but was much easier to clean up afterwards
=)