transpose first and last names in a cell?

  • Thread starter Thread starter johngo
  • Start date Start date
J

johngo

Greetings, Excel Gods and Godesses,

I have a file with names in a column, in the format FIRSTNAM
LASTNAME.
I would really love to have them in the format, Lastname, Firstname.

Is there a fairly painless way to switch/transpose the names, an
ideally add a comma after the lastname?

Thanks,
Confused, Joh
 
gods and godesses were on leave, they are having monday morning blues
so the devil is answering this call.

as far as i know, there are two ways of doing this.

1. use Text to Column - Fixed Width to split the data into two cells
then you the Concatenate function to get the two values together agai
with a comma in between. this will require helper columns (at leas
two).

2. use VBA macro to do this without using helper columns.

which one do you prefer? the devil's in a benevolent mood today, so yo
have the choice
 
I have the formula for this same problem that I had a few months back
It may be fairly painless (depends on how you look at it).

If your name (FIRSTNNAME LASTNAME) is in A1, the formula is

=right(A1,len(A1)-find(" ",A1))&", "&left(A1,find(" ",A1)-1)

and copy down until your requirements are satisfied.
 
Assuming there's a space between the first and last names, you can use text to
columns, but you don't want fixed width. You want delimited, with the
delimiter = space.

For a formula to do the conversion:
=MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1)-1)

Convert the formulas to their values by Edit/Copying the column of formulas,
then select the column with the original names and Edit/Paste Special and
select values. Then you can delete the column with the formulas.
 
Back
Top