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

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

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