reformat a cell from last name, first to first space last?

[Guest]

I downloaded a spreadsheet with a column in it containing peoples names.
They are listed with the last name first then a comma and the first name or
first initial. I want to change them to first name and last name sparated by
a space for data base purposes to use in a mailing. Is there any easy way to
do this?

 

[Gord Dibben]

Shannon

You may find it is better to have the names in separate cells rather than change
to first, last in one cell.

Most third-party apps prefer it this way.

You could use Data>Text to Columns space delimited to split into two cells.

If you want to swap in-cell, see Chip Pearson's site for formulas.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

 

[Franz Verga]

Nel post *Shannon* ha scritto:

I downloaded a spreadsheet with a column in it containing peoples
names.
They are listed with the last name first then a comma and the first
name or first initial. I want to change them to first name and last
name sparated by a space for data base purposes to use in a mailing.
Is there any easy way to do this?

Hi Shannon,

suppose you have a name in D9, you can copy this formula in E9:

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

then fill down to rearrange all the names. At the end, if you want to delete
the original column you have to first Copy & Past Special, Values the column
with the rearranged names.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Hi Shannon,

Let's say you have a name (in the "LastName, Firstname" format) in cell A1,
you can put this formula in another cell to get the "Firstname Lastname"
format you desire:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(",",TRIM(A1)))&"
"&LEFT(TRIM(A1),FIND(",",TRIM(A1))-1)

(note that the trim function is not necessary if you know for sure that your
original name text will have no leading or trailing spaces)

 

[Guest]

Gord:

After you do text to columns, can't you just concatenate the two cells (in
reverse, obviously), adding a space in between?

 

[Gord Dibben]

Yes, that method could be used as easily as Chip's formulas.

But Chip's formulas do not require the names to be split up first, so one less
step.


Gord Dibben MS Excel MVP