Solved

# Switch names around

Posted on 2012-09-17
268 Views
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
Question by:Seamus2626

LVL 39

Expert Comment

try this

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

A1 text like : Smith John
0

LVL 5

Expert Comment

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

LVL 39

Accepted Solution

this is one more

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

LVL 31

Expert Comment

If all just single first and last name:

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

Author Closing Comment

Legend!

Thank you
0

LVL 50

Expert Comment

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 Comment

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

## Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.