| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Text to columns
I am trying to split 700 names with phone numbers. They are in column A.
There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
It looks like you have name space - space number.
Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Biff
Microsoft Excel MVP Congratulations, Biff ! Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Thank you, Max.
Hopefully, I haven't lowered the standard! Biff Microsoft Excel MVP "Max" wrote in message ... Biff Microsoft Excel MVP Congratulations, Biff ! Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
I think that's a common feeling for everyone--that and the "did you really mean
me?" that goes with it <vbg. Congrats! "T. Valko" wrote: Thank you, Max. Hopefully, I haven't lowered the standard! Biff Microsoft Excel MVP "Max" wrote in message ... Biff Microsoft Excel MVP Congratulations, Biff ! Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Thanks, Dave.
You're still the man! Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I think that's a common feeling for everyone--that and the "did you really mean me?" that goes with it <vbg. Congrats! "T. Valko" wrote: Thank you, Max. Hopefully, I haven't lowered the standard! Biff Microsoft Excel MVP "Max" wrote in message ... Biff Microsoft Excel MVP Congratulations, Biff ! Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Add my congrats to this thread Biff.
Welcome aboard. A few months ago you posted that becoming an MVP was your goal so what's your next goal? Gord On Mon, 02 Jul 2007 13:41:44 -0500, Dave Peterson wrote: I think that's a common feeling for everyone--that and the "did you really mean me?" that goes with it <vbg. Congrats! "T. Valko" wrote: Thank you, Max. Hopefully, I haven't lowered the standard! Biff Microsoft Excel MVP "Max" wrote in message ... Biff Microsoft Excel MVP Congratulations, Biff ! Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Well earned, well deserved. Congratulations.
"T. Valko" wrote: Biff Microsoft Excel MVP |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Thanks, Jerry!
Biff Microsoft Excel MVP "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Well earned, well deserved. Congratulations. "T. Valko" wrote: Biff Microsoft Excel MVP |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Another way - select your column - making sure that you have a couple of
empty columns to the right of the one selected. click on "text to columns". Choose "Delimited", then click on the next button. in the next dialog box, there is a list of Delimiters on the left. Check the box in front of "other" and then put in a hyphen. click next, then finish. "B.W." wrote: I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Thank-you that worked. But I would like to keep my phone number in one column
if possible. I tried to regrouped the two columns with the phone number, but I am not having any success. "mfdou" wrote: Another way - select your column - making sure that you have a couple of empty columns to the right of the one selected. click on "text to columns". Choose "Delimited", then click on the next button. in the next dialog box, there is a list of Delimiters on the left. Check the box in front of "other" and then put in a hyphen. click next, then finish. "B.W." wrote: I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
B.W. wrote:
Thank-you that worked. But I would like to keep my phone number in one column if possible. I tried to regrouped the two columns with the phone number, but I am not having any success. "mfdou" wrote: Another way - select your column - making sure that you have a couple of empty columns to the right of the one selected. click on "text to columns". Choose "Delimited", then click on the next button. in the next dialog box, there is a list of Delimiters on the left. Check the box in front of "other" and then put in a hyphen. click next, then finish. "B.W." wrote: I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 Assuming you still have an original try using text to columns and leaving it as fixed width you can then place your dividers where you need them. If you place the dash between the name and phone number in it's own column you can just mark it not to import or just delete it later. gls858 |
|
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
Ok, it might be that those spaces are not standard char 32 spaces. They
might be char 160 non breaking spaces. In the EditReplace operation: Find what: hold down the ALT key and using the *numeric keypad* type 0160 let up on the ALT key then type the dash then hold down the ALT key and using the *numeric keypad* type 0160 Replace with: , (comma) Replace All Close Then do the text to columns Biff Microsoft Excel MVP "B.W." wrote in message ... I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
If using the hyphen as the deliminator separates the two parts of your phone numbers, you can put them back together fairly easily. make sure there is an empty column to the right of the two columns with the phone number and enter =(cell&-&cell) in the first cell of the empty column - for instance, =(A1&"-"&B1) This will give you back your phone numbers with the hyphen. "T. Valko" wrote: Ok, it might be that those spaces are not standard char 32 spaces. They might be char 160 non breaking spaces. In the EditReplace operation: Find what: hold down the ALT key and using the *numeric keypad* type 0160 let up on the ALT key then type the dash then hold down the ALT key and using the *numeric keypad* type 0160 Replace with: , (comma) Replace All Close Then do the text to columns Biff Microsoft Excel MVP "B.W." wrote in message ... I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
for the 3 part phone number, just expand the formula
=(A1&"-"&B1&"-"&C1) "mfdou" wrote: If using the hyphen as the deliminator separates the two parts of your phone numbers, you can put them back together fairly easily. make sure there is an empty column to the right of the two columns with the phone number and enter =(cell&-&cell) in the first cell of the empty column - for instance, =(A1&"-"&B1) This will give you back your phone numbers with the hyphen. "T. Valko" wrote: Ok, it might be that those spaces are not standard char 32 spaces. They might be char 160 non breaking spaces. In the EditReplace operation: Find what: hold down the ALT key and using the *numeric keypad* type 0160 let up on the ALT key then type the dash then hold down the ALT key and using the *numeric keypad* type 0160 Replace with: , (comma) Replace All Close Then do the text to columns Biff Microsoft Excel MVP "B.W." wrote in message ... I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
|
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Text to columns
I forgot to mention - you have to then copy the formula by clicking in the
cell with the formula in it, then move the mouse over the lower right corner until the cursor turns into a solid black cross, then click and drag the formula all the way down to the bottom of your list. "mfdou" wrote: If using the hyphen as the deliminator separates the two parts of your phone numbers, you can put them back together fairly easily. make sure there is an empty column to the right of the two columns with the phone number and enter =(cell&-&cell) in the first cell of the empty column - for instance, =(A1&"-"&B1) This will give you back your phone numbers with the hyphen. "T. Valko" wrote: Ok, it might be that those spaces are not standard char 32 spaces. They might be char 160 non breaking spaces. In the EditReplace operation: Find what: hold down the ALT key and using the *numeric keypad* type 0160 let up on the ALT key then type the dash then hold down the ALT key and using the *numeric keypad* type 0160 Replace with: , (comma) Replace All Close Then do the text to columns Biff Microsoft Excel MVP "B.W." wrote in message ... I think I did everything you requested but i get a message " cannot find the data your searching for" "T. Valko" wrote: It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu EditReplace Find what: <space-<space That is, type in a space then the dash then another space Replace with: , (type in a comma) Replace All Close With those cells still selected Goto the menu DataText to Columns DelimitedNext CommaFinish Biff Microsoft Excel MVP "B.W." wrote in message ... I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
| help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
| merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
| Excel is automatically doing "text to columns" upon paste text. | Excel Discussion (Misc queries) | |||
| Linking text columns with text and data columns | Excel Worksheet Functions | |||
Hybrid Mode
