excel cell changes a number with 3 decimals to full number by itself

4
votes

I have an excel workbook with several cells with format number + 3 decimals. Works fine with many users in my office (we used Excel 2007)

However there are a couple of PC's which when they type a number in the cell with 2 decimals, it works great BUT as soon as they type 3 decimals, it gets converted to a full number, without the comma.

So if they type 1,45, then in the cell looks like 1,450, and on the formula bar 1,45 (which is correct)

BUT if you type 1,455 gets converted to 1455 !!! On the cell itself looks like 1455,000, while on the formula bar the value is 1455

Makes no sense...I have already spent 4 hours looking for a solution on the internet, as well as checked every option available in the OPTIONS menu of Excel, but I was unable to find a solution...

2
excel
I'm not sure how it would affect it as you describe but can you check under Excel Options > Advanced > Editing Options. Do you have anything set under Automatically insert a decimal point? – barry houdini
@pnuts, yes there are macros, and they are enabled. Type for the cells is number, with 3 decimals, no thousands separator – user1135218
@barry, "automatically insert a decimal point" is OFF, no tick. – user1135218
Yes, I couldn't re-create that behaviour, even with that option turned on - seems that Jüri's suggestion is probably correct? – barry houdini

2 Answers

5
votes

I managed to reproduce the behaviour in Excel 2003:

I guess you have Decimal separator = "," and Thousands separator = "," (Tools-> Options ->International for Excel 2003). Such behaviour is triggered if the two separators are the same.

0
votes

I,ve had the same exact problem. Went into Options=>Advanced and Unchecked Use system separators and given my preference. Works fine now. Nice to know others had same peculiar problem.

BR