Forum Discussion
Excel changing numbers, removes the decimal point.
I have a little spreadsheet I use to calculate gas mileage, costs, per tank and annually etc. Excel has decided to eliminate the decimal point in the number field for gas, so I type 12.215 and excel shows 12215 and I cannot get it to accept the decimal point. The cells are Number format. So the calculations are wrong. I tried making a new spreadsheet - no joy. I tried to copy rows where the calculation is correct-nope doesn't work. I tried reformatting the cells (cleared contents, deleted them) and selected Number format again. Nope doesn't work. Saw a suggestion about making sure that Automatic decimal insertion isn't selected under the Advanced Options for working in Excel. Super annoying.
37 Replies
- Yes. Under File\Options\Advanced tab, scroll to the Editing section and you will see a checkbox for "Use System Separators." With that box unchecked and the decimal separator being a comma instead of a decimal, and the cell formatted to three decimals and suppress the thousands separator, then I can duplicate your issue. Go to that setting and change your decimal separator back to a decimal (or check the box to use your local machine region settings).
IMHO, the setting changes the behaviour for entire file. I doubt if it affects behaviour when values in one range ignores separator and in another one - not. Both have the same format applied.
- Yes, I know it's an application level setting and affects the entire file. Please note that I said it was probably changed, then the additional two numbers keyed, and then changed back.
I was able to replicate the data he is seeing (correct and incorrect numbers), your opinion notwithstanding, so I do think it is a possibility and the OP should double check the application and machine settings to confirm which one it is using and whether or not they are set correctly.
- Actually, I did duplicate it.
With system separators unchecked, I played with the decimal and thousands separators and keyed some numbers (which displayed 13,935,000 when I entered 13.935). Then I went back into the options and checked the system separators box (which I think reverts the setting your machines regional system setting) and that changed it to 13935.000 like what you are seeing).
So, maybe the setting was changed, the data was keyed, and then the setting was changed back? Maybe check your system separators (File\Options\Advanced - Use System Separators)?
Or, maybe your machines region/language settings (Control Panel\Region and Language - Additional Settings)?
I can't duplicate your issue either, so I would guess it's an application setting or machine setting that maybe was changed?
Is that for any file or for some specific one? If the latest perhaps you may attach a sample.
SergeiBaklan See attached sheet. I highlighted the problem. Green cells are fine, yellow cells will not accept a decimal so instead of 12.215 gals, I get just the string 12215...argh. I have been keeping this file for years. Thanks for the help!
Mark, I'm not able to reproduce, all numbers are entered correctly (left column here)
At the same time, if inspect the document we see some extra XML data exists
Cleaned it in attached file, not sure how that could affect.
So far have no idea what it could be.
- Oh I am using Excel 365 on windows 10 machine
