Excel is forcing numbers on me!

[Mister_joe]

Hi folks,
I type the numbers shown below into cells in a worksheet but Microsoft Excel modifies them.

Data typed into a cell	Data displayed by Excel
999,999,999,999,999.99	999,999,999,999,999.00
99,999,999,999,999,999	99,999,999,999,999,900.00
99,999,999,999,999,999,999	99,999,999,999,999,900,000.00
999,999,999,999,999,999,999	999,999,999,999,999,000,000.00
999,999,999.99	1000000000

Is there a way to stop this from happening? I am writing a vba code that uses the numbers typed into certain cells and I do not want Excel to alter them because that may lead to the wrong results.


Thanks.

[JKwan]

may be typing an apostrophe in front of your numbers is what you need?

'999,999

[ranman256]

Expand the cell length.
Also, the value you see IN the cell may be different in the function bar (the Fx box,atop the cells C & D).
The function bar IS THE EXACT NUMBER, but the cell box may round or reformat for viewing. (dumb)

[Bob Phillips]

It's all down to the floating point engine in Excel. Although it can display 30 decimal points, the precision for a specified number is confined to 15 significant figures.

[Paul_Hossler]

It's ugly, but if you enter long numbers as text (with a leading single quote: '99999999999999999) internally you can get more precision using Decimal-typed variables

Sub numbers()
    Dim v As Variant
    
    'Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10.
    'The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and
    'ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is
    '+/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is
    '+/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.
    
    'Note
    'At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable
    'to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.
    
    v = CDec(Replace(ActiveSheet.Range("a1").Text, ",", vbNullString))
    MsgBox v
End Sub

If you start doing math, I suspect that you'll get inaccuracies.

I remember seeing a BCD package for excel a long time ago that allow almost unlimited precision by treating string 'numbers' on a character by character basis