Format Number

A

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
 
R

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!
 
P

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
 
J

JoeU2004

RedCattledogs said:
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 -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top