parse text string

D

Dave F

Let's say I have a bunch of names, such as:

John Doe
Jane Doe
Jim Doe

and I want to re-arrange them as
Doe, John
Doe, Jane
Doe, Jim

I can figure out that a formula to do this is: =MID(A2,FIND("
",A2,1)+1,LEN(A2)) & ", " & LEFT(A2,FIND(" ",A2)) etc.

However, if the name is John M Doe the above returns M Doe, John which
isn't too helpful.

So is there a single formula I can use to parse names, whether they
are First Last or First Middle Initial Last to return Last First or
Last First Middle Initial?

I'm also open to using VBA if that is a better solution.

Thanks,

Dave
 
T

T. Valko

Assuming that *all* names are either format:

John Doe
John M Doe

=MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))>1,3,1),LEN(A2)+1)

Returns:

Doe, John
Doe, John M

Biff
 
T

T. Valko

Just in case the method you're using to view these groups causes a line wrap
problem:

=MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-
LEN(SUBSTITUTE(A2," ",""))>1,3,1),
LEN(A2)+1)

Biff
 
H

Harlan Grove

Dave F said:
Let's say I have a bunch of names, such as: ....
However, if the name is John M Doe the above returns M Doe, John which
isn't too helpful.

So is there a single formula I can use to parse names, whether they
are First Last or First Middle Initial Last to return Last First or
Last First Middle Initial?
....

So there'd either be just two or three words in each name and only the
last word would be the last name? If so,

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,
MID(A1,FIND(" ",A1)+1,256)&", "&LEFT(A1,FIND(" ",A1)-1),
MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256)&", "&
LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)))

But that won't work for multiple word surnames like de la Hoya or
Lloyd George. There's no simple formula that works for all names.
 
J

JLatham

Not to mention the other oddities such as
John Jones III
John Brown, Sr.
or names with an honorific as
John Smith, PhD
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top