Basic Question (help the rookie)

yojeff

New Member
Joined
Jan 25, 2007
Messages
33
Ok, I have a employee roster that I need the first and last names switched. Each cell has a person's first name and last name in it. All the names are in a single column:

John Smith
Joe Blow
Jim Shmuckatelli
Jane Doe

I cant draw the table here, but each name is in it's own cell. I need to switch the first and last names so it reads like this:

Smith John
Blow Joe
Shmuckatelli Jim
Doe Jane

I need to do five hundred names. Any Ideas?

Thanks Again (will post my super secrete mango salsa recipe if you can get me out of this jam)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

SteveInAus

New Member
Joined
Aug 4, 2011
Messages
7
If you only have records with two names, e.g. Bill Smith
and you dont have records with three names e.g. Joanne Kelly Johnson you can do it this way

Have your original names in Column A
Put a copy of them in Column B (saving A as the back-up for when things go wrong)
Highlight Column B (just your data, no headings) and go Data, Text to Columns, then Choose Delimited, then select space as your delimiter, then OK
This will split Column B into two Columns (B and C) (make sure you dont have any data in columns to the right, or this will overwrite that data - you may need to insert a few spare columns just in case
Then insert a new column next to those columns at Column D
and concatenate your two new columns
=concatenate(C1," ",B1) - there is a space in between the inverted commas

You can still use this method if you have some people with three names, but you will need to be more careful in the way you put them back together (concatenate)

Hope this helps
Fellow Rookie
 
Last edited:

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

Or with one formula in B1 -
=TRIM(RIGHT(SUBSTITUTE($A1," ",REPT(" ",99)),99)) &" "&LEFT($A1,FIND(" ",$A1)-1)

and copy down.

If you then want to remove your original list select Column B, Copy then Paste Special - Paste Values into column B, delete Column A.

This formula will not work with second Christian names or Jr and Sr etc suffixes to the Surname.

hth
 

Watch MrExcel Video

Forum statistics

Threads
1,129,796
Messages
5,638,385
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top