How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Zabo04 Your Own Question
Zabo04
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

I've downloaded an excel document with numbers this

Customer Question

I've downloaded an excel document with numbers this long 1028418416021567839150703. However once downloaded Excel converts these long strings of digits into scientific notation format and displays the number this way 1.23457E+1
How can I get the original value to display properly without scientific format?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Zabo04 replied 1 year ago.

Excel has a storage limitation for accuracy, as do all software programs and languages. In a programming language you would write a custom data type of an array of integers to store a number that large, and all the custom functions you need (addition, subtraction, etc). So if you copy and paste that number into excel 1028418416021567839150703, it is stored with 15 significant digits, the last being rounded.

Expert:  Zabo04 replied 1 year ago.

Now, if you take 1028418416021567839150703 and copy it, format the cell in Excel to text before you paste, then paste. It will show all digits. You can use functions to convert the text to a number (using the Value function), however excel will round it back to 15 sig figs.

Expert:  Zabo04 replied 1 year ago.

If you have just the downloaded Excel file, there are only 15 significant figures stored there. The rounding happened in storage and it's gone. If you have the original numbers and are building the excel spreadsheet, you could do some tricks to make it work. Effectively making it text, splitting it into substrings of specified length, and writing cells that split everything out to do any math, then convert those numbers (of no more than 15 significant figures) to text, and concatenate the text.

Expert:  Zabo04 replied 1 year ago.

I have had to do this personally, which is why I know about it, but here is the Microsoft Knowledge base article: https://support.microsoft.com/en-us/kb/65903.

Expert:  Zabo04 replied 1 year ago.

From a very low level computer science perspective what is happening is the memory allocation process limits the number of bits for a defined type. Microsoft has not created a data type for Excel to storage large numbers. Because it would require an array or list based ADT (advanced data type) in the actual code for Excel, it reduces the speed optimizations available on the processor (basically the specialized commands that are sent to the CPU which are predefined to access hardware optimizes which is several orders of magnitude beyond any speed increase that software can provide). So there are some work arounds, but I need more details on what you are trying to do.

Expert:  Zabo04 replied 1 year ago.

If you only need to view in a data table, just formatting the cells as text and pasting in will work. The formulas are where things get excessively difficult.

Expert:  Zabo04 replied 1 year ago.

Also note, at 15 significant figures, some rounding occurs in intermediate steps, so although they are stored, they may not all be accurate depending on the function Excel runs.

Customer: replied 1 year ago.
Hi the issue is that i'm downloading these numbers (9000 total) from a web based reporting system. They all show truncated the moment i download them as a CSV file from the website.where i generated this specific report.
Customer: replied 1 year ago.
I've been told to do this but it does't work for me.
I save the file from the system on my desktop without changing it at allthan open new excel sheet.than click on Data than click on "from text" it will open a window to grab a file and you can choose the file you saved from hasoffersafter it will upload a new window will called "text import wizard"you choose delimited and do next than you mark the 3 first check box and do nexton Step 3 you will see all the info from hasoffers file on the small windowits important you mark the numbers we need (affiliate_sub2) and check the box "Text" instead of generalthan you click on finish and it will ask you to "import data". Press OK and you will see the full number
Expert:  Zabo04 replied 1 year ago.

Can you post the web address?

Expert:  Zabo04 replied 1 year ago.

I'd the CSV is truncated, there is nothing excel can do. They are lost at that point.

Expert:  Zabo04 replied 1 year ago.

Can you only download the file or does the page show a table (html), that you can copy and paste from then into .csv? I'm trying to look for where the truncation is happening, exactly. If the csv file is rounded it's before that and that could be a browser issue, if you can see the table with full data as an html you could copy and paste it. If that works it can most likely be scraped, or look into a plug-in for the browser, or possibly link excel to direct download the data. There are a couple of options, if I could see the site it would help, of course I do not know if you can share the data even if you wanted to. Just trying to find a solution for you.

Related Microsoft Office Questions