4

This:

3.1415926535

produces:

3.141592654

How can I have it output the whole number as is?

I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to that. The default setting seems to be to round numbers to the number of decimals in 3.141592654 (9).

I will stress that I cannot use TEXT() to format numbers explicitly, because the source data (coming in from 3rd party) appears over many lines (10000+) and I just need to reference the exact input values as is.

The "right way" to do this would be to set PLAIN TEXT format on the cell. However with existing value like 3.1415926535, that would end up with loss of data because the output will be 3.141592654. This is not acceptable for 10000+ rows.

11

The solution is quite simple. Press this button to increase the number of decimals: enter image description here

Now it will show the correct amount of decimals:
enter image description here

Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest.

  • That's fine, but I think I need to edit this setting on a file level - the contents in the same column are randomly text and numbers. Current workflow also is to replace a sheet completely by adding a new one, and deleting the old one. I reference this source data sheet from another one by the name of the sheet. With your suggestion, I would have to remember to press that button, having that column selected, everytime I import new data/new sheet. – Jonny Mar 19 '15 at 4:14
  • I am sorry but the explanation makes no sense to me. I have a cell set to # format and to 8 decimal places. I enter the first # below and Google Sheets continually changes it to the second # rounding back to 6 decimal places then 0's after that? 374,999,173.66771734 - 374,999,173.66771700 I also try in the next column =round(F3, 8) and the function works for 6 decimals and below but above 6 just shows 0's not the true #? You said above "Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest." I would be very happy if it did in fact work this – Ronald Russo Jr. Sep 23 '18 at 14:14
1

If you are working in a field where that degree of decimal precision is important, then perhaps Google Sheets (where functional changes happen regularly, and many are not notified to the users) is not the best tool to use.

All applications round numbers at some point. If you need engineering precision in how this is done, then you may need to investigate applications that give you more control.

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.