• Status: Solved
• Priority: Medium
• Security: Public
• Views: 312

# Switch names around

Hi,

I have a list of names in a column

e.g.

Smith John
Obama Barrack
Beckham David

I would like some code/Formala that can switch these around so that the column displays

John Smith
Barrack Obama
David Beckham

Thanks
Seamus
0
Seamus2626
1 Solution

Commented:
try this

= CONCATENATE(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)-1),  LEFT(A1,FIND(" ",A1)))

A1 text like : Smith John
0

Commented:
use the following formula in the adjacent column to get the desired result

=CONCATENATE(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)), " ", LEFT(A1, SEARCH(" ",A1,1)))
0

Commented:
this is one more

=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" " &LEFT(A2,FIND(" ",A2)-1)
0

IT & Database AssistantCommented:
If all just single first and last name:

=MID(A3,FIND(" ",A3,1)+1,LEN(A3))&" "&LEFT(A3,FIND(" ",A3,1))
0

Author Commented:
Legend!

Thank you
0

Microsoft MVP ExcelCommented:
Hello,

If your data is very regular, with just one first and one last name, then the above formula will be ok.

But will there ever only be two strings in the cell? First and last? How about

Pink

or

Then the above will fail. The following will not:

=TRIM(MID(A1,FIND(" ",A1&" ")+1,99)&" "&LEFT(A1,FIND(" ",A1&" ")))

Concatenate() is not required. All it does is present a long way to apply the & operator.

Mary Jane Smith
John F Kennedy
Hillary Rodham Clinton

Do you have names like that? How would you want to treat them? What about two first names? What about two last names?

cheers, teylyn
0

Author Commented:
Thanks for the update Teylyn, it was a quick and dirty job and so the original answer did the trick.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.