Custom formatting for a cell

[brucefrank]

I need some help…

In Excel 2003, I am entering in a column of cells; different numbers that I
want formatted a specific way.

For example:

I enter this number: 31002770004304330

I want to format them like this: 3100-27-7000-430-4330

After I entered the first series of numbers without the dashes, I went to
format cells, selected custom, and enter in 0000-00-0000-000-0000 and applied
this format.

What happen is Excel changes by last 4 numbers in the string from 4330 to
4300 when I enter the data. Why?

I am using the numeric key pad enter a couple hundred of these and don’t
want to have to take time to type in each “-“ for the series of numbers.

What am I missing???


Thanks for any help you can give me.

 

[Trevor Shuttleworth]

I think you'll find that Excel is limited to numbers of up to 15 digits,
hence your data is truncated even before you apply your format.

Regards

Trevor

 

[Dave Peterson]

Format that column as text.
Then type your 17 digit "numbers"

Then use a formula like:
=LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,4)&"-"&MID(A1,11,3)&"-"&RIGHT(A1,4)
in an adjacent cell.
drag the formula down.

Convert this column to values
(Edit|Copy, edit|paste special|values)

Delete the original column.