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

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?

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

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 DataText 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

On Sat, 24 Jun 2006 09:14:01 -0700, Shannon
wrote:

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?

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

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

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

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 desi

=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)


"Shannon" wrote:

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?

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

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" wrote:

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 DataText 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

On Sat, 24 Jun 2006 09:14:01 -0700, Shannon
wrote:

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?

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

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

On Sat, 24 Jun 2006 10:30:02 -0700, bman342
wrote:

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" wrote:

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 DataText 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

On Sat, 24 Jun 2006 09:14:01 -0700, Shannon
wrote:

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?