May 18 2020 09:11 AM
May 18 2020 09:11 AM
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.
May 18 2020 11:50 AM
Is that for any file or for some specific one? If the latest perhaps you may attach a sample.
May 19 2020 12:00 PM
@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!
May 19 2020 01:36 PM
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.
May 21 2020 08:57 PM
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?
May 21 2020 09:04 PM
May 21 2020 09:15 PM
May 22 2020 04:04 AM
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.
May 22 2020 09:50 AM
May 26 2020 09:50 AM
@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.
May 26 2020 04:41 PM - edited May 26 2020 07:00 PM
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.
May 27 2020 12:12 AM
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?
May 30 2020 08:04 AM
@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.
May 30 2020 09:32 AM
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.
May 30 2020 09:34 AM
That is strange. You have also currency format with two dots, currency aligned to left
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
2) Open New Excel file, do nothing but check this setting
is it set or not and if set when how
3) Is this setting unchecked or not and if unchecked which separators are defined
4) Stay on cell A1, when B1, Ctrl+1 and check which format is applied (General or not)
into A1 12.212
into B1 250
into C1 =A1*B1
What we will see in all 3 cells?
Nov 06 2020 09:07 AM
@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