Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimited numbers
I am trying to delimited 1,240 rows of numbers which I need to separate as
phone numbers. I need the phone numbers to be written as 314-923-0417. Right now the number is written as 3149230417. My problem is when I delimite, the system will leave off the 0 thus giving me 314-923-417 after I concatenate all my phone numbers with a 0 in the parameter are missing the 0's. I have tried formating the column as text, then as special formating. So far nothing has worked. If anyone knows how to accomplish this task please please let me know. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimited numbers
Select them all, format as 000-000-0000
Bob Umlas Excel MVP "NDF" wrote in message ... I am trying to delimited 1,240 rows of numbers which I need to separate as phone numbers. I need the phone numbers to be written as 314-923-0417. Right now the number is written as 3149230417. My problem is when I delimite, the system will leave off the 0 thus giving me 314-923-417 after I concatenate all my phone numbers with a 0 in the parameter are missing the 0's. I have tried formating the column as text, then as special formating. So far nothing has worked. If anyone knows how to accomplish this task please please let me know. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimited numbers
If the numbers are all of a similar format, you could use custom format
000-000-0000 -- David Biddulph "NDF" wrote in message ... I am trying to delimited 1,240 rows of numbers which I need to separate as phone numbers. I need the phone numbers to be written as 314-923-0417. Right now the number is written as 3149230417. My problem is when I delimite, the system will leave off the 0 thus giving me 314-923-417 after I concatenate all my phone numbers with a 0 in the parameter are missing the 0's. I have tried formating the column as text, then as special formating. So far nothing has worked. If anyone knows how to accomplish this task please please let me know. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimited numbers
In another reply I suggested custom format 000-000-0000.
If, however, you do particularly want to split the number into the different segements, you can do that using Data/ Text to Columns, but as you don't want to lose the leading zeroes you need to set Text as the format for each column at the final stage of the Data/ Text to Column Wizard *before* you exit from the wizard. If you allow it to exit setting the output as numbers (or general), then merely formatting the cell format as text afterwards won't recover the lost data. -- David Biddulph "NDF" wrote in message ... I am trying to delimited 1,240 rows of numbers which I need to separate as phone numbers. I need the phone numbers to be written as 314-923-0417. Right now the number is written as 3149230417. My problem is when I delimite, the system will leave off the 0 thus giving me 314-923-417 after I concatenate all my phone numbers with a 0 in the parameter are missing the 0's. I have tried formating the column as text, then as special formating. So far nothing has worked. If anyone knows how to accomplish this task please please let me know. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimited numbers
Instead of splitting the numbers up and concatenating them, how about just
giving the cells a special format. Select the range format|cells|number tab|Special|phone number. or format|cells|number tab|custom and type: 000-000-0000 in the type box. But if you want, you can join them with something like: =text(b1,"000")&"-"&text(c1,"000")&text(d1,"0000") NDF wrote: I am trying to delimited 1,240 rows of numbers which I need to separate as phone numbers. I need the phone numbers to be written as 314-923-0417. Right now the number is written as 3149230417. My problem is when I delimite, the system will leave off the 0 thus giving me 314-923-417 after I concatenate all my phone numbers with a 0 in the parameter are missing the 0's. I have tried formating the column as text, then as special formating. So far nothing has worked. If anyone knows how to accomplish this task please please let me know. Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert tab delimited files to pipe delimited? | Excel Discussion (Misc queries) | |||
Comma Delimited Numbers | Excel Discussion (Misc queries) | |||
need help on delimited | Excel Worksheet Functions | |||
Totaling the number of comma delimited numbers in a column | Excel Worksheet Functions | |||
Converting negative/positive numbers to a CSV (Comma-delimited .TX | Excel Worksheet Functions |