Forum Discussion
Please update Excel to handle more than 15 digit numbers!
Not sure why a new limit needs to be set, not with modern technology and hardware.
And if a new limit is required, they should set it to whatever is reasonable by current tech/standards. If necessary, they can have it be a user setting (replace digits with zeros after "x" digits) if folks are having performance issues.
Just because a new limit may need to be set doesn't mean they should leave it as is, because it's broken as is. They could at the very least extend it to solve a majority of use cases out there. At least 64 ought to solve the vast majority of issues out there. Surely that must be doable.
The limit is determined by the storage allocated to hold a number in a standard IEEE format. There is always a compromise to be struck between the storage demands & processing time required for a calculation, and the accuracy achieved. Some calculations such as the solution of large ill-conditioned systems of simultaneous equations can run into problems but I think it very unlikely that you are concerned with the precision to which numbers are represented.
It appears to me that the 'numbers' you wish to manipulate are in fact text strings that happen to based upon the digits 0-9 rather than A-Z. In which case the limit appears to be 32,767 of which 1024 may be displayed as a single row within the cell. This is not a workaround, a credit card number cannot be meaningfully doubled or square-rooted, so text is the appropriate format.
The catch is that Excel is like an eager puppy in the way it attempts to convert anything that could be considered as a number in order to perform numerical calculation. SergeiBaklan​ has shown ways of suppressing this behaviour by setting options. Another approach is to prefix a possible number by a single apostrophe, say when loading the data from VBA.
Once Excel is allowed to convert the text to a number the original text cannot usually be recovered and would need to be read once more from the database.