Excel 2010- long number corruption

Question

I have a set of data, received in .csv format, in which I have a set of long UPRN numbers. I am involved in data matching processes related to new IER voting regulations.
When I open the .csv file the numbers are in scientific format (2.00E+11, every number the same). All of these numbers are supposed to be different. For one spread sheet I already did the below:

I opened the file in Notepad, saved to .txt Select Data > From Text In the Text Import Wizard Step 1, choose Delimited In the Text Import Wizard Step 2, choose Comma In the Text Import Wizard Step 3, select your long ID column and choose Text data format. Then finish.
This worked for one spread sheet. All of the numbers were revealed in an Excel Worksheet, all numbers were different as they should be. But when I tried to save as .csv, all of the numbers went back to 2.00E+11 (all the same number).

Then when I tried to do the above to the two other workbooks I had, for some reason it didn't work, even though I completed each step in the exact same way. Even when I opened in notepad, they were all the same numbers (2.000E+11, which when I converted to text just came to 2000030000000). I managed to convert the original .csv to a workbook, convert that to notepad, and when I scrolled down past all the symbols I could see that the original data was in there amongst the mess.
How can I get this data to reveal itself?! Any suggestions gratefully received, or indeed please let me know if there is a link to this elsewhere.

Many thanks

Answer 1

This is happening because Excel sees these cells as numbers, but because they are so long they are being interpreted as Floating Point numbers. Number formatting precision in Excel maxes out at 15 digits as explained here.

What you should probably do is handle each value as a string. Adding a single ' at the beginning of every cell with data in it should fix this issue up, or you can format all the cells as a string or text.

This method is not without it's limitations, however. If you are going to be processing this data then you may need to have some way to sanitize the data before feeding it into whatever processes it.