Excel converts 4.009 value to 4009. Why?

[st_george]

Hello,

I have the following issue:
Whenever my number in a cell has three decimals (maybe even more, I haven't checked this, I apologize. I do not have access to the file at the moment), the dot separator is removed.

For example if I type the following number: 4.009, it is converted to 4009.
However, if I type the number with only two decimal places, like: 4.09, then Excel rounds it to 4.

Why is this happening?

And how can I fix this issue?

I would be grateful for any kind of help.

[Glenn Kennedy]

Hard to tell, without seeing the file. How is the affected cell(s) formatted? Is there any VBA running in the background?

have you tried the hairy man's solution: TIOTIO???

[st_george]

Thank you for the quick reply Glenn!

The cell is formated as Number.
I do not think any VBA code is implemented in the mentioned .xlsx file.

[Glenn Kennedy]

OK. So can you then post the file?

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[joeu2004]

Some things to look at, although they do not jibe with all the facts as you present them....

It sounds like you want period to be the decimal point. In other words, 4.009 is 4 + 9/1000; and 4.09 is 4 + 9/100.

Be sure that the Region and Language control panel specifies that.

And be sure that the Excel Language option and Advanced System Separators option do not override that.

Lastly, be sure that the Excel Advanced Lotus Compatibility options are disabled. I cannot imagine how they might cause what you see. But I have seen seemingly "inexplicable" behavior that is caused by those options.

[joeu2004]

PS....

Also, be sure that the Excel Advanced option "Automatically insert decimal point" is disabled. That should have the opposite impact than you describe; but perhaps you turned things around inadventently.

And be sure that there is no Conditional Formatting applied to the cell(s).

[st_george]

Thank you for the useful advices joeu2004!

I took one of them and checked the "Advanced Settings" in "Regional and Language" in Control Panel.
For some reason both "Decimal symbol" and "Digit grouping symbol" used the dot ("."), which seem to create confusion. I set the "Digit grouping symbol" to comma (",") and now everything works fine in Excel.

Thank you once again for the help!!!