Excel formatting issue

[DanAuber]

I'm trying to enter 16 digit numbers into Excel. There seems to be a bug here:

Try this -
i)open a new workbook and goto cell A1
ii) Enter Number:7845907834568970
iii) Format the cell to Custom 0000000000000000 (16 zeros)
iv) Now change the 0 at the end of your number to a 9 - Excel changes it straight back to a 0

Has anyone come across this before - and if so have they managed to solve it ?

Thanks for any help

Dan

 

[yogia]

Hi Dan:

EXCEL can only display upto 15 digits for a number.

If you need a 16 digit ID you should format the cell as TEXT (precede the entry with an apostrophe) and then enter the 16 digits.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

Dan,

FYI: You can search in Excel's help file ([F1]) for "specifications and limits" to see more limits of excel.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[anationalacrobat]

The other thing to be careful about, Excel will strip preceeding zeroes off of all numbers unless you get your formatting right. That means that a zip code like 00123 would become 123. You can put an apostrophe in front of manually-entered numbers to protect agains this sort of thing. This will store the number as text and since numbers with preceeding zeroes usually aren't intended for mathematical operations (i.e. they're part numbers or serial numbers), that should serve you just fine.

 

[Welshbird]

I have codes for products that do the same thing (in losing leading zeros) and someone (I suspect Skip) told me I could use this:

=text(a2,"0000000000000")

for a 13 digit code.
It's helped me out on countless occasions.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]