Using Data Validation in Google Sheets to only allow numbers, commas and spaces

user_9640665 0
Viewed 681

I am creating an input google sheet to accept just numbers, commas & spaces - examples listed below. - At a basic level, I just want to exclude the use of A-Z / a-z.

80092382
800
800,876
98672102,20192210

I would like it to exclude anything like:

Hey 01
987 blue
black 1 white
orange

I am getting stuck early on, where I'm trying to only allow text with only numbers in, or excluding anything with letters in. I have tried the following lines of code within the RegexMatch formula but it either accepts lines with text, or rejects my numbers.

=RegexMatch(L5,"\d")
- This one rejects the numbers.
    
=RegexMatch(to_text(L5),"\d")
- This rejects only where there are no numbers in the cell - So 'Hey 01' is accepted.

=RegexMatch(to_text(L5),"^\d")
- Same issue, if the cell starts with a number '987 blue' then its accepted

I've attempted a few other ways, such as using the NOT function at the beginning & using other regular expressions. If anyone can point me in the right direction then that would be much appreciated.

Test sheet

google-sheets regex
2 Answers

You can use

=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(C2:C9), "^\d+(,\s*\d+)*$"), "Good", "Error"))

The regex matches

  • ^ - start of string
  • \d+ - one or more digits
  • (,\s*\d+)* - zero or more repetitions of
    • , - a comma
    • \s* - zero or whitespace
    • \d+ - one or more digits
  • $ - end of string.
user_38329700

You can test the content with this type of regex:

=arrayformula(if(regexmatch(to_text(A1:A),"([^\d.,])"),"Error",))

enter image description here

user_157844150
Related