how to seperate cell contents into two cloumns ?

[TimR]

I have a list of about 4000 names...however...the names, both first and
last, are all in one column as follows: column A = lastname1, firstname1.
What I need to do is copy all the last names in column A and insert them in
the adjacent cells in column B. I then would have a worksheet of names as
follows:

A B
row 1 - firstname1 - lastname1
row 2 - firstname2 - lastname2
etc

In essence...I need to edit each cell, copy the text before the "," copy or
move that to the adjacent cell and delete the , and the text (name) that
came before it in column 1

How can this be done...thanks, Tim

 

[David McRitchie]

Hi Tim,
I have macros on my join.htm page that I would
use myself. But this can be easily done without macros.

Before starting you might want to globally change
the column data
", " to "," using Ctrl+H

Use Data, text to columns, defined delimiter,
on next panel choose comma

To reverse the column positions Select the
column with the lastname grab the left border
below the column heading letters and SHIFT+drag
to between the columns you want it to appear.

Reference:
http://www.mvps.org/dmcritchie/excel/fillhand.htm#mouse

FWIW, for a name and address list, my preference is
phone number, 'lastname, firstname/ (with the comma), address columns
with all columns being text including phone numbers and zip codes.

 

[Gord Dibben]

Tim

My method using Data>Text to Columns.

First select the column of names the Edit>Replace

What: space
With: nothing

Replace all.

Then with column selected, Data>Text to Columns>Delimited>Next>Comma>Finish.

Select the B column header and slide mouse pointer down to edge of B1 to get a
4-headed pointer.

Hold SHIFT and move column B to the left and drop it.

Column A will shift right and you're done.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

ooops!

Sorry David..........missed your identical post.

Little slow after a big meal.


Gord

 

[TimR]

Thanks David and all...worked fine...seemed embarrassingly simple...now one
more issue that has come up.
I now have my first names in one column next to the last name column...task
one.
Now is there a way to eliminate 'automatically the middle initials ? My
source only has full names and can not eliminate the middle initial in the
files. I have in my first name columns the following:

Robert T & Mary S
Tom G & Betty B

Other than going through all 4000 or so cells...is there a way to remove the
individual middle initials in this column only so I get the following:

Robert & Mary
Tom & Betty

Thanks again, Tim