1

I have an excel spread sheet with many columns and rows of values. But I noticed different decimal separation if I open it either in excel or in my browser (file-preview of OneDrive). I took a snapshot of the same spreadsheet lines, e.g. look at row 13. The left part is opened in standard excel program office365 and on the right the same is opened in browser.

As we can see the values in the browser, e.g. 22,826253 are correct with correct decimal punctuation, however the same value on the right 22,826,253 has a second decimal separator!! How can I change this to normal?

I already looked here, but changing the cell format to "general" didn't help: with some columns it did help, with others (e.g. time stamp) not.

What can I do please?

enter image description here

EDIT:

Not sure if this is helpful, but if I load the spread sheet for the first time into excel program, it shows many ######-values in cells:

enter image description here

Only once I widen the cells width does this wrong formatting 22,826,253 appear.

Improve this question
6
  • 2
    In the US , is not the decimal separator but the thousands separator. so in the US 22,826,253 is 22 million 826 thousand 253 not 22 with a decimal of 826253
    – Scott Craner
    Commented Jan 21, 2021 at 19:10
  • Thanks @ScottCraner, I know for certain that the values is supposed to be 22,826253 - a decimal value. So how can I change the thousands separator 22,826,253 in the one excel program to instead show decimal 22,826253?
    – MJimitater
    Commented Jan 21, 2021 at 19:12
  • 1
    So you need to find from the source whether it is meant to be 22 million 826 thousand 253 or 22 with a decimal of 826253 then you can use NUMBERVALUE() and dictate the what are the decimal and thousand separators to convert it correctly.
    – Scott Craner
    Commented Jan 21, 2021 at 19:12
  • Hi @ScottCraner, I added another edit to my question, maybe thats any helpful?!
    – MJimitater
    Commented Jan 21, 2021 at 19:28
  • Are you opening a csv file? or an xls file? What are your Windows Regional Settings?
    – Ron Rosenfeld
    Commented Jan 21, 2021 at 20:48

1 Answer 1

Reset to default
1

The left hand side is not showing a decimal separator but a thousand separator which is dependent on the local convention which the browser has it's own localization which is different from the one set on your computer.

There appears to be a scaling issue because 22,826,253.00 is not equal to 22.826253 even after you convert formatting use the same localization. The different is a round scaling factor of a million, so I suspect somewhere in the calculation there could be a 1,000000 which is interpreted as 1 million online and just 1.0 locally (again for uniforming formatting) or something similar. Since thousand separators are optional, most software just ignore them and so an incorrect 1,00,0000 or 1000,000 would still be parsed.

Improve this answer
8
  • Thanks for your answer @Itai, one question: "There appears to be a scaling issue because 22,826,253.00 is not equal to 22.826256" -> you mean "...not equal to 22.826253"?
    – MJimitater
    Commented Jan 21, 2021 at 19:21
  • 1
    Sorry, corrected
    – Itai
    Commented Jan 21, 2021 at 19:22
  • I understand your explaination, thanks! But how can I go about to solve it? Is there a setting I can apply?
    – MJimitater
    Commented Jan 21, 2021 at 19:23
  • Is NUMBERVALUE() helpful, that @ScottCraner proposed?
    – MJimitater
    Commented Jan 21, 2021 at 19:24
  • 1
    No. That widening in standard behavior when showing numerical values that do not fit. You can easily have gotten back to seeing numbers by using a smaller font. To debug the issue, look at the formula used to computer a cell showing the unexpected value. If there's something in there that is not written with the local convention, that's your issue. If there isn't, then look at the cells it refers to and see if those have something, repeat as needed. Hopefully the formulas are not that complex and aren't too many cells to trace.
    – Itai
    Commented Jan 21, 2021 at 19:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .