Only allow whole numbers but display with '.' on the thousand for easy read

Question

I want to apply data validation to my column so as to only accept whole numbers.

However I want these to be displayed with a dot so as to make it easier to read later on.

e.g. input = 14354 which is valid and then displayed 14.354

the data validation regular expression I am ussing is:

=regexmatch(to_text(A2);"^\d+\.*\d+$")

and the custom formatting is:

#,##

for most this working fine, large numbers are displayed with the '.' and things it shouldnt accept it is rejecting.

However, in the case of numbers which are entered with a decimal point as these are hidden, it is accepting it as valid.

It is also changing the format to auto atic and reading as date such entries like: 15.4

I should point out that I am using sheets in spanish and therefore the , is the marker for decimal places.

What am i missing here??

Answer 1

Try this one:

=and(regexmatch(to_text(A2);"^\d+(\.\d{3})*$");mod(A2;1)=0)

• Improved your formula to only accept a dot when it is followed by 3 numbers (this way, we invalidate the date e.g A2)
• Combining the improved formula of yours and Aresvik's modulo answer, we need to check if the value does not have decimal. (this way, we invalidate the decimal e.g A6)
• When both returns true, this shall confirm that the number inputted is a whole number with no decimal and not a date.

Output:

Invalid inputted values:

• A2 - 15.4
• A6 - 16412,212

Answer 2

Select the cell range then go to Data > Data validation...

Add a custom formula rule:

=mod(A1;1)=0