How block insertion of values with decimal points/commas?

**JohanVB** · 08-30-2017, 11:31 AM

Hi!

Is there a way to for certain fields block manual insertion of numerical values with decimal points/commas?

i.e. one should only be allowed to enter integers.

for some fields, but not all.

**tim201110** · 08-30-2017, 11:45 AM

data validation, settings, Allow Whole number

**JohanVB** · 08-30-2017, 11:51 AM

@tim201110
Thanks! nice and easy

follow-up:
Is it also possible to restrict manual input to real numbers with only one decimal?
i.e. 4.1 and 5 are allowed but not 5.67

there is way to use custom made data validation, but can that we be used for formatting?
e.g. so that #.# would limit input to one decimal value.

**tim201110** · 08-30-2017, 12:03 PM

=LEN(10*MOD(H2,1))=1, this is a condition for H2

**JohanVB** · 09-05-2017, 07:36 AM

=LEN(10*MOD(H2,1))=1

How is this inserted in Excel?
=LEN(10*MOD(H2,1))=1 makes Excel say there is a an error in formula.
LEN(10*MOD(H2,1))=1 is accepted by no values whatsoever abide with rule, or so Excel says.

Do you have to do it for each cell you want to validate?
say first cell in column of interest is H2.
Can you validate all below cells with the same formula, or you need to define the rule for each cell?