Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default how to seperate cell contents into two cloumns ?

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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 903
Default how to seperate cell contents into two cloumns ?

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





  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cell contents into two cloumns ?

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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cell contents into two cloumns ?

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default how to seperate cell contents into two cloumns ?

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell References [email protected] Excel Discussion (Misc queries) 2 November 16th 06 12:37 AM
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 07:01 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Conversion of Cell Contents into a Functional Worksheet name ? GMJT Excel Worksheet Functions 1 August 21st 05 05:59 PM
Function syntax to compare cell contents ES Excel Worksheet Functions 2 May 18th 05 04:53 PM


All times are GMT +1. The time now is 09:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"