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

"T. Valko" wrote in message
...
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

"Dave F" wrote in message
ps.com...
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