Format Number

[Abdul]

I am trying to format the number 12345612345123456 to display as
123456 12345 123456 by entering a custom format code 000000 00000
000000. But the result display like 123456 12345 123400. Instead of
the last digits 56 it displays 00. ??

Thanks

 

[John McCabe]

Had the same problem and they were able to help. Mr. Excel only likes 15
numbers, so they had me format it as text and it works great now. Sorry
excel does not like over 15 numbers.

 

[RedCattledogs]

I have been using Excel since 1980s, and this 2007 version drives me crazy!
I tried to forrmat the numbers in Text as you said, and 5.6789E +15 came out.
How can I input larger than 15 digit numbers without Excel changing the
numbers?
I would appreciate help!

 

[Pete_UK]

Either pre-format the cell as Text before you enter the number, or
include a preceeding apostrophe, eg:

'1234567890123456789

The apostrophe will not show in the cell.

Hope this helps.

Pete

 

[JoeU2004]

I have been using Excel since 1980s, and this 2007 version drives me
crazy!
I tried to forrmat the numbers in Text as you said, and 5.6789E +15 came
out.
How can I input larger than 15 digit numbers without Excel changing the
numbers?

This problem arises with all versions of Excel, not just Excel 2007.

Excel recognizes only the first 15 significant digits of numbers, and it
ignores (and strips) leading zeros of integral numbers (e.g. 000123).

If you format the cell as Text __before__ entering numeric strings, or if
you prefix the numeric strings with an apostrophe (single quote), you can
enter numeric strings of 16 or more digits and numeric strings that with
leading zeros that you want to preserve.

Also, if you are importing from a text file, the Import Wizard allows you to
specify that a column is text.

But if you format the cell as Text __after__ entering a numeric string of 16
or more digits or a numeric string with leading zeros, you are SOL. Excel
has already modified value. The only remedy is to re-enter the numeric
string as described above.


----- original message -----