Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Cell References | Excel Discussion (Misc queries) | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions |