0

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

0

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.

  • Thanks a million for your response. This method did reveal the number in it's entirety, unfortunately it was the incorrect number. It is frustrating as the correct number is stuck in there somewhere, and the method worked previously. But many thanks for your help, I will try to retrieve the original data and see how it goes! Many thanks once again. Emma – Emma Jul 21 '14 at 11:39
  • Once the number has been changed there's no chance the original data will still be there if you saved the file. Good luck with retrieving your data however. – Michael Frank Jul 21 '14 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.