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

You can do it with
datatext to columnseasy
or look in the help index for
FIND


--
Don Guillett
SalesAid Software

"TimR" wrote in message
...
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

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"TimR" wrote in message ...
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

Tim

My method using DataText to Columns.

First select the column of names the EditReplace

What: space
With: nothing

Replace all.

Then with column selected, DataText to ColumnsDelimitedNextCommaFinish.

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

On Fri, 9 Feb 2007 18:06:36 -0700, "TimR" wrote:

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

ooops!

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

Little slow after a big meal.


Gord

On Fri, 09 Feb 2007 19:27:17 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Tim

My method using DataText to Columns.

First select the column of names the EditReplace

What: space
With: nothing

Replace all.

Then with column selected, DataText to ColumnsDelimitedNextCommaFinish.

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

On Fri, 9 Feb 2007 18:06:36 -0700, "TimR" wrote:

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

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

"David McRitchie" wrote in message
...
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"TimR" wrote in message
...
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