15 digit max in Excel might mean that I have do my spreadsheet all over again :(

[masterexcel365]

Hi

I didn't realize there is a limit on the maximum number allowed in all versions of Excel (15 digits long max).
I've made an error by manually entering loads of values only to discover that I lose the accuracy after the first 15 digits which are replaced by zeroes in affect. It was nearly a 3 hour piece of work which it looks like I will have to repeat unless anyone know a way to help me.
Since then I've saved and exited my spreadsheet. Later on a customer came back to me to say that that the numbers I gave them are inaccurate as they only only go up to 15 digits for accuracy.
I then researched this on the net to find that I should have formatted the column as Text before copying in the number that is more than 15 characters.
Does anyone know if there is any way to get the numbers back or is will I have generate my spreadsheet all over again.
Kind Regard James

 

[mole999]

i believe the values are gone, thats the level of accuracy excel works to and stores, someone else will be able to tell you if you do math on a text value, can you get a result in text that hasn't been adjusted to 15 decimal places

 

[joeu2004]

I then researched this on the net to find that I should have formatted the column as Text before copying in the number that is more than 15 characters. Does anyone know if there is any way to get the numbers back or is will I have generate my spreadsheet all over again.

The latter. As mole999 notes, once Excel has replaced the 16th+ characters with zeros, there is no math that can recover them, unless you know a mathematical relationship (and can tell us). Usually, these 16+ digit "numbers" are IDs, not mathematically related.

So you must return to original data that has the 16+ digit IDs.

But formatting the cells as Text before "entering" them might still not work. If you actually import the data from a file, you should use the Data / Get External Data / From Text File feature. In the last step, set the column to Text.

If you are copy-and-pasting data, you might be SOL. It depends on details. What are you copying from?

(FYI, I might not be able to comment further during the next few days. But someone else might jump in.)

 

[masterexcel365]

If you are copy-and-pasting data, you might be SOL. It depends on details. What are you copying from?

(FYI, I might not be able to comment further during the next few days. But someone else might jump in.)

Hi Joe
I'm copying from output results from SQL Management Studio and pasting the SQL table values into excel. Basically I need to connect into nearly 100 customer systems, run a SELECT statement and put the output back into Excel, I'll test it first by making the relevent column a Text format in any case.
I like your idea of using a mathematical formula to work it out - they are barcode id numbers so that might be possible.
Thanks
Matt