delimited numbers

[Guest]

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.

 

[Bob Umlas]

Select them all, format as 000-000-0000
Bob Umlas
Excel MVP

 

[David Biddulph]

If the numbers are all of a similar format, you could use custom format
000-000-0000

 

[David Biddulph]

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.

 

[Dave Peterson]

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")