Excel changing numbers, removes the decimal point.

Copper Contributor

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
Oh I am using Excel 365 on windows 10 machine

@MarkMarkwj 

Is that for any file or for some specific one? If the latest perhaps you may attach a sample.

@Sergei Baklan  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!

@MarkMarkwj 

Mark, I'm not able to reproduce, all numbers are entered correctly (left column here)

image.png

At the same time, if inspect the document we see some extra XML data exists

image.png

Cleaned it in attached file, not sure how that could affect.

So far have no idea what it could be.

 

@Sergei Baklan 

Interesting.

If I check the document I get a different result.

XML-Daten.png

@Detlef Lewin 

That's on initial file or one I attached? The latest is cleaned.

@Sergei Baklan 

That was the initial file.

 

@MarkMarkwj 

 

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?

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?
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).

@JMB17 

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.

@JMB17 Thanks all I had tried this previously, doesn't seem to work for me. I also just tried to make a brand new spreadsheet and the same error appears??? closed and reopened the spreadsheet and made the suggested changes, retyped the decimal point all to no avail still have the same issue.

@MarkMarkwj 

 

You also checked Windows number formats under region/language settings in control panel? 

With both the decimal separator and group separator set to a period (instead of group separator being a comma), I get exactly what you are seeing (new inputs have the decimal moved 3 places without affecting the existing numbers).

 

I downloaded your file, keyed another entry, and get exactly what you are seeing (keyed as 12.215 and 1.719 and the decimal moved 3 places after hitting enter).

 

If this is not your issue, I'm afraid I'm at a loss for any further suggestions.

 

 

@MarkMarkwj 

Mark, how exactly "same issue" looks?

- you open new workbook

- type numbers like 12.345 into the cells from top to down

- in rows from 1 to 12 (or so) Excel shows as it is entered, i.e. 12.345

- in next rows starting from #13 Excel shows 12345 ignoring decimal point

 

Or that's another behaviour?

@Sergei Baklan Sergei, et al, yes you describe the issue correctly above.

 

I tried making a new worksheet. Typed in 12.212 as an example of the data I want to enter. Excel shows it as 12212. No decimal.

 

I went to the Control Panel and checked the region/language, re-entered these as USA and English. I also when to File-Options-Advanced-Editing Options and re-entered the separators as a "period" for the decimal separator and a "comma" for thousands separator. Spreadsheet still only will show 12212. 

 

Attached is today's test. Interestingly under the second column (Miles) Excel takes my enter of 250 and turns it into 0.25????

As it stands Excel is useless for such a simple set of calculations. 

@MarkMarkwj 

 

Would you mind posting screenshots of your Excel editing options (File\Options\Advanced - editing section) and your machines region settings (Start\Settings\Time and Language\Region\Additional date, time & region settings\Change date, time, or number formats\Additional settings).

 

Changing the region formats to U.S. / English is not what I was talking about. You have to go further into the additional settings to see what the system group separator is.

 

I attached screenshots of the settings I'm talking about. When open your spreadsheet, I can't duplicate your issue unless I change application settings or system settings. It just doesn't seem like a problem with your workbook because it works fine for me.

@MarkMarkwj 

That is strange. You have also currency format with two dots, currency aligned to left

image.png

Not sure if that you applied such formats or something is wrong in Excel behaviour. Could we make clean test please?

1) Nothing change in Windows settings, just check and record which they are

Win+R control international Additional settings

image.png

 

2) Open New Excel file, do nothing but check this setting

image.png

is it set or not and if set when how

3) Is this setting unchecked or not and if unchecked which separators are defined

image.png

4) Stay on cell A1, when B1, Ctrl+1 and check which format is applied (General or not)

5) Enter

into A1 12.212

into B1 250

into C1 =A1*B1

What we will see in all 3 cells?

@JMB17  Sorry I sort of gave up on this and haven't checked in oh...5 month...

see attached: I think I checked these before still no joy...thanks for all the help and attention