Need some help with name replacement type formulas
 

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

Need some help with name replacement type formulas
 

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" wrote:

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

Need some help with name replacement type formulas
 

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


"CLR" wrote:

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" wrote:

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