Basic Question (help the rookie)

[yojeff]

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)

 

[SteveInAus]

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

 

[ukmikeb]

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