Forum Discussion
Excel changing numbers, removes the decimal point.
SergeiBaklan 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.
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)
5) Enter
into A1 12.212
into B1 250
into C1 =A1*B1
What we will see in all 3 cells?
Great, thanks for sharing
- Glad to hear you got it working.
JMB17 Duh...I had checked the regional settings before (as suggested) but maybe I didn't reset them -reset all the settings and spreadsheet appears to be working as before. Yea! Thanks to you and Sergei for all the patience and help!
- Your last screenshot of the windows settings kinda looks like the thousands separator is a period and not a comma (which would definitely cause the problem you are having), but it's really hard to tell.
It's the only thing I can think of that would cause your issue. When I open your workbook, I can input the numbers just fine. So, my thought is that it is an application or windows setting pertaining to your number formatting (assuming you're working on the same machine that was used to input the data). - No, I mean the windows regional settings (under control panel). If you refer to Sergei's post on 5/30/20 9:34 AM, Item #1, you will see an example screenshot.
You did follow up to Sergei's post w/ a screenshot, but it's too small to read as the screenshot was of the entire screen. JMB17 I think this is what you wanted. Thanks for the help!
- Can you include a clean picture using the windows snipping tool of the decimal and thousands separator under the windows regional settings?
Also looks like the issues start in line 8, it's ok in the previous lines, Column C and Column F, should show the decimal for number of gallons (C-10, 11, 12 point something; F show the dollar amount per gallon 1.859, 1.999, whatever)
SergeiBaklan Hi fellows-I followed the instructions (sorry for the blurry screenshots it was just easier than retyping it all)
1. I unchecked the box to "Automatically insert a decimal point" under Editing Options.
2. I also validated that the symbols listed in "Use system separators" are a decimal point".", for the Decimal separator; and a comma"," for the Thousands separator.
I dropped the whole spreadsheet. with the changes looks like starting with row 8 the spreadsheet doesn't accept the decimal point in column C "Gals" or Column F.
Arrghhh!
I don't have an answer on what is happening, that's why I asked to clarify every item I asked. Otherwise we could only make the guesses what is actually happens.
From his screenshot, the fixed decimal is turned on. Maybe when you said to check the setting the OP thought you meant to check the box.
The only thing I've tried that moves the decimal to the right like that is if the decimal and thousands separator are both a period. Although the OP said he checked those previously (back in May) and confirmed they were set to a period and comma, respectively, his recent screenshot really looks like the grouping symbol is a period and not a comma. If I blow it up and look at the digit grouping field, the separator symbol doesn't look like a comma as I don't see any dark pixels below the bottom of the numbers next to it (when I change my setting between a period and comma, the OP's looks more like a period to me).
But, it's hard to say, the screenshot is pretty blurry and my eyes aren't what they used to be.
At the same time 12.121 it transformed to 12121
- For one thing, you need to uncheck "Automatically insert a decimal point" under Excel's advanced options. That will cause 250 to become 0.250.
One screenshot disappeared from above, this setting
Unfortunately your screenshot hard to recognize, it looks like
but let operate by one sample, with figures from your post. Do I understood correctly that
- typed in A1 12.121 is transformed to 12121.00
- typed in B1 250 is transformed to 0.25
- to both A1 and B1 is applied General format
- not sure how 12.121/250 shall give 20.6 and why 121121.00/.025 gives 1, that's totally unclear
- in your regional settings you have dot, 2 and comma as here
- in advanced settings you have unchecked "Automatically insert decimal point"
- in Advanced settings you have unchecked "Use system separator"
Please confirm if each point as above or indicate the difference. If issue is still exist it's better to have sample file instead of screenshot to play with formats on it, and please indicate in file comments or by text in another cell what did you type if result is different from that.
Thank you.
SergeiBaklan RE: Excel changing numbers, removes the decimal point. Sorry-My issue is Excel doesn't let me key in numbers for number of gallons from a fill-up -12.121 is changed in Excel to12121.00. I have checked the region settings. The screen shot followed your advice from May to set up a new excel file with as an example cell A1 shows 12.121; B1 with 250 (which is changed as you see to 0.25; and calculate A1/B1. So instead of returning a calculation of 20.6 MPG I get 1.
I can't find a way to insert the decimal in the correct place. Excel removes the decimal.
To reiterate this is a mileage spreadsheet I have kept for years suddenly in May it doesn't calculate correctly. Now I got a new laptop in May but the spreadsheet shouldn't have been affected-Windows 10; MSOffice 365 is the same program on my old laptop. Thanks for the help
Could you please articulate more exactly what is your question. This thread is too old and has quit many posts to re-read them all and trying to make a guess what is applied to your case.
On screenshot I see number 0.25 shown as 0.25 with General format.
SergeiBaklan Hi Sergei as I wrote earlier: Sorry I sort of gave up on this and haven't checked in oh...5 month...
Followed your steps below: still no joy the screen shots of the control panle and the excel options posted on JB's post
