Moving last names to another column (Excel 2000 SR1)

Topic

New Reply

In an older message, someone posted an easy way to separate first and last names when they had been entered in one column, separated by a comma. I want to do that now, but after scanning all 16 pages of Excel posts, have determined that this was one of the posts that was eliminated. Does anyone have this information?

Thanks,
Tia

Reply | Quote

Replies

A simple way is
1. Make sure that there is an empty column to the right of the column with names.
2. Select the column with names.
3. Select Data | Text to Columns…
4. In the first step of the wizard, select Delimited.
5. In the second step, select the Comma as delimiter.
6. Click Finish.

If you have to do this often, you can create a macro to do it for you. Post back if you need this.

Reply | Quote

Thanks. That’s easier than what I figured out, which was to copy the column to Word, convert to text, then convert back to columns with the comma as the separator. Then copy and paste the columns into 2 empty columns in Excel. I didn’t think I could do it from within Excel. I like your solution better.

Tia

Reply | Quote

I often run into this problem so here are three notes I made to which I often refer. You may need to adjust them to meet your needs but there are written “conceptually”:
==================================================
Excel – last name sort

Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

=RIGHT(A1, LEN(A1)-FIND(” “, A1))

When the all the names have been done, select all the full names and all the last names. Then, choose (from the menu) Data, Sort. Sort on column B.
================================================
Excel – lastname, firstname -> firstname lastname

Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

=RIGHT(A1, LEN(A1)-FIND(” “, A1))&” “&LEFT(A1, FIND(“,”, A1)-1)
=============================================================
Excel – lastname, firstname -> lastname firstname (two columns)

Put the names in a spreadsheet starting in A1. Then, place the formulas below in B1 and B2 then replicate them all the way down until the last name in the list is covered.

B1 =LEFT(A1, FIND(“,”, A1)-1)

B2 =RIGHT(A1, LEN(A1)-FIND(” “, A1))

Reply | Quote

I often run into this problem so here are three notes I made to which I often refer. You may need to adjust them to meet your needs but there are written “conceptually”:
==================================================
Excel – last name sort

Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

=RIGHT(A1, LEN(A1)-FIND(” “, A1))

When the all the names have been done, select all the full names and all the last names. Then, choose (from the menu) Data, Sort. Sort on column B.
================================================
Excel – lastname, firstname -> firstname lastname

Put the names in a spreadsheet starting in A1. Then, place the formula below in B1 and replicate it all the way down until the last name in the list is covered.

=RIGHT(A1, LEN(A1)-FIND(” “, A1))&” “&LEFT(A1, FIND(“,”, A1)-1)
=============================================================
Excel – lastname, firstname -> lastname firstname (two columns)

Put the names in a spreadsheet starting in A1. Then, place the formulas below in B1 and B2 then replicate them all the way down until the last name in the list is covered.

B1 =LEFT(A1, FIND(“,”, A1)-1)

B2 =RIGHT(A1, LEN(A1)-FIND(” “, A1))

Reply | Quote

Thanks. That’s easier than what I figured out, which was to copy the column to Word, convert to text, then convert back to columns with the comma as the separator. Then copy and paste the columns into 2 empty columns in Excel. I didn’t think I could do it from within Excel. I like your solution better.

Tia

Reply | Quote

A simple way is
1. Make sure that there is an empty column to the right of the column with names.
2. Select the column with names.
3. Select Data | Text to Columns…
4. In the first step of the wizard, select Delimited.
5. In the second step, select the Comma as delimiter.
6. Click Finish.

If you have to do this often, you can create a macro to do it for you. Post back if you need this.

Reply | Quote