Custom formatting for a cell
 

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 dont
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.

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


"brucefrank" wrote in message
...
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.

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.

brucefrank wrote:

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 dont
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.

--

Dave Peterson