Separating Names

[Merlyn]

I have an Excel worksheet containing several hundred names
with the last and first names entered in the same column
such as Smith, John W.
I would like to separate these so that the Last name is in
one column and the First name is in another. Is there a
way to do this without retyping all of them?

 

[Bob Umlas]

Look at Data/Text to Columns. Perhaps pick Delimited and then choose comma.

 

[Domenic]

Assuming that your list of names is in Column A, and that the format is
consistent (last name + comma + space + first name including any
initial)...

B1, copied down:

=LEFT(A1,FIND(",",A1)-1)

C1, copied down:

=RIGHT(A1,LEN(A1)-(FIND(",",A1)+1))

Then select your new columns > Copy > Paste Special > Values, and delete
your old column, if you wish.

Hope this helps!

 

[Guest]

Hi Merlyn.

First, you need to insert a column to the right. Next, select the whole
list of names and select Data and then Text to Columns. In the window,
select Delimited, click Next, select Comma, then click Finish.

If you have more than one comma in any name, you'll need to add more than
one blank column to the right. You may want to add a few just in case, then
delete them once you've cleaned it up.