Supressing zeros in number columns when no decimal values are entered

[KJay]

Hi everyone.

I have a column whose datatype is number set to two decimal places. Everyone knows what this means is, that each cell in that column will always show two decimal places. That's fine when the values entered happen to have decimals. But, what if I wanted to format the column such that only the values entered are shown?

For example, if a user entered 3, then I want the display to read 3. If the user entered 4.2 then I want the display to read 4.2. And if the user enters 6.13 then the value displayed should read 6.13. I'll be doing some math on these numbers so I'm guessing it's best to leave the datatype as a number and not change it to say, text.

Does anyone know how I can do this?

Thanks!

KJ

[XOR LX]

Hi.

Not sure I understand. What restrictions are there that you haven't mentioned which prevents the format simply being set to General?

Regards

[FDibbins]

Hi, welcome to the forum

Try setting the format to General

[KJay]

Hi XOR LX.

I want the field to be numeric because I'll be doing some calculations on the values entered in the column. It seemed appropriate to keep the column's datatype a number, rather than general. I thought that if I kept the column a numeric datatype, I wouldn't get into any trouble or encounter any weird, strange behavior by trying to do calcs with a non-numeric field.

Just trying to be on the safe side here.

[KJay]

Hi, FDibbins. Glad to be here.

I wanted to avoid any funky one-off instances of getting into trouble by doing calcs on a non-numeric field. I've run into this before (not in Excel) and I thought it'd be best to keep numbers formated as numbers.

Are you aware of any situation - however odd or uncommon, where the results could be off somehow if I did calcs on a field whose datatype was General?

Thanks.
KJ

[XOR LX]

Can you give an example of such "weird, strange behavior" that might occur with a range formatted as General but which would be avoided if the format were instead Number?

Regards

[KJay]

I cannot, off hand. I have run across this kind of thing before (not with Excel) where, using data that looks like a number but is really text (or some other format) can sometimes not give expected results. It's not at all common, but you know what they say. Once bitten...

Can you foresee any situation where doing calcs on numbers in cells formatted as General would cause a problem, however remote?

Thanks.

[XOR LX]

Can you foresee any situation where doing calcs on numbers in cells formatted as General would cause a problem, however remote?

Not that wouldn't also occur if that format were set to anything else.

Typing '1.2 (with a leading apostrophe) into a cell formatted as Number (set to as many decimal places as you like) will not mean that that entry is considered any more numeric (it will not be) than if I'd entered it into a cell formatted as General.

Regards

[KJay]

Excellent to know. Ok then. General it is!

Thank your for the exceptionally quick responses.

[FDibbins]

General format is just that - it will accept pretty much any input, and display as entered. If you enter a number, it will display exactly as entered...if you enter text, that too will be displayed as entered.

If you, for instance, have a column where some cells are text, and others numeric entries, only the numbers will be added with =SUM()

[Pete_UK]

Yes, there can be problems, like the formula =C2*2 will return 0 if C2 is empty but will return the #VALUE error if you put a space in C2, even though it looks the same. However, the formatting of C2 has no bearing on that result in these two cases. I think what you might be thinking of is applying data validation to ensure that only numbers can be entered, so that subsequent calculations will not cause that type of mismatch error. That's a different thing than formatting.

Hope this helps.

Pete