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
|