Swapping order of names

[dboatrt]

I have a column of firstname and firstname lastname and need to change it to lastname, firstname and firstname. I've tried text to columns but because of the ones w/ multiple firstnames I cannot do it without alot of extra manipulating.

Does anybody have a quick fix?

For example the way it currently appears:

Jane and John Doe

I need it to read as: Doe, Jane and John

 

[barry houdini]

If data is in A1 try this formula in B1

=MID(A1&", "&A1,FIND("^^",SUBSTITUTE(A1," ","^^",3))+1,LEN(A1)+1)

 

[dboatrt]

This did not work. Not every row in the column have multiple first names such as Jane and John Doe. Some in the column are singles such as Jane Doe. Will this make a difference? Sorry I should have included that bit of info in the first message.

 

[pgc01]

This did not work. Not every row in the column have multiple first names such as Jane and John Doe. Some in the column are singles such as Jane Doe. Will this make a difference? Sorry I should have included that bit of info in the first message.

Hi

Yes you should, because that must be included in the formula.

Barry's formula will, however, work also in this case, you just have to replace the hardcoded number of spaces:

=MID(A1&", "&A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)+1)