Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Cell < Today() | Excel Worksheet Functions | |||
Reference Cell in custom format???? | Excel Discussion (Misc queries) | |||
custom cell formatting | Excel Discussion (Misc queries) | |||
Why won't my conditional formatting display in the cell | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions |