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

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
Asked:
Seamus2626
1 Solution
 
Pratima PharandeCommented:
try this

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

Add this formula in B1

A1 text like : Smith John
0
 
sameer_goyalCommented:
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
 
Pratima PharandeCommented:
this is one more

=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" " &LEFT(A2,FIND(" ",A2)-1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

=MID(A3,FIND(" ",A3,1)+1,LEN(A3))&" "&LEFT(A3,FIND(" ",A3,1))
0
 
Seamus2626Author Commented:
Legend!

Thank you
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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

Madonna

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.

But what about

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
 
Seamus2626Author Commented:
Thanks for the update Teylyn, it was a quick and dirty job and so the original answer did the trick.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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