Limit cell inputs to disallow fractions

[sbernard]

I have a spreadsheet that requires inputs from many individuals. There are cells for SKU length, width and height. These values are used in calculations. The problem is some individuals enter fractional values. For example, instead of entering 2.5 they'll enter 2 1/2. Is there a way to limit inputs so fractions can't be entered?

I tried using data validation, but was unsuccessful.

[MarvinP]

Hi sbernard,

Try the attached with data validation.

[ben_hensel]

well, assuming they're entering the fractions correctly, it doesn't really matter because the fractions can be used by excel to do math anyway?

Another thing to do is, set the cell format from general to number beforehand, so even if they enter a fraction, it's displayed as a decimal. Personally that would be my preferred solution--let people enter the data however they want, but display it the "correct" way.

My biggest concern would be that the fractions are entered wrong, so they get treated like a text string instead of a number, ie someone putting in "1/2" instead of "0 1/2"; excel would assume "Jan 2nd", not "one half". But "excel thinks you meant date when you meant fractions" is a slightly different problem... and preformatting the cells to "number" precludes that from happening anyway.

[jason.b75]

Your calculations should still work, the underlying values will still be decimal.

Try setting the cell format as number instead of general, then enter a fraction in the cell.

[sbernard]

Thanks Ben! Sometimes I try and make things too complicated.

[sbernard]

Marvin - Thanks for your input. However, I want to allow decimals, just not fractions.

[MarvinP]

This will work great if .5 <> 1/2. However they are the same to Excel.

Good luck with your search.