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

Question

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

Try `REGEXMATCH(TO_TEXT(L5), "\d+(?:,\d+)*")`. If spaces are allowed anywhere, `"\s*\d+(?:\s*,\s*\d+)*\s*"` — Wiktor Stribiżew, Jun 08 '21 at 13:33
@wiktor Thanks for the quick response. I get the same problem where cells with just words in are removed, but if I include a number in the cell, it is accepted. — Paul Marvell, Jun 08 '21 at 14:03
Not sure I get it, look [here](https://regex101.com/r/EgxJv7/1). — Wiktor Stribiżew, Jun 08 '21 at 14:11
Paul, did you check the regex demo at https://regex101.com/r/EgxJv7/1 and did you use `=REGEXMATCH(TO_TEXT(L5), "\d+(?:,\d+)*")` in Google Sheets document (not in any online tester)? — Wiktor Stribiżew, Jun 08 '21 at 14:27
@wiktor Ive tried the tester and directly in the data validator tool in google sheets. I have created a test sheet to show my issues. https://docs.google.com/spreadsheets/d/1Bz4ov4aRkz49TRKD0xFkA5chFscgiimu4jLxtoagCqQ/edit?usp=sharing — Paul Marvell, Jun 08 '21 at 15:54
Try `=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(C2:C9), "^\d+(,\s*\d+)*$"), "Good", "Error"))` — Wiktor Stribiżew, Jun 08 '21 at 16:04
@Wiktor That formula works as expected. Thanks for your help. Im going to test it further. — Paul Marvell, Jun 08 '21 at 16:12
If my answer did not solve your issue please consider updating the question. — Wiktor Stribiżew, Nov 28 '21 at 19:46

score 2 · Accepted Answer · answered Jun 08 '21 at 16:16

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.

score 0 · Answer 2 · answered Jun 08 '21 at 14:30

0

You can test the content with this type of regex:

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

answered Jun 08 '21 at 14:30

Aresvik

4,484
1
5
18

Thanks for this. I did try this regular expression earlier to the same issue. Although the formula regexmatch worked as intended, putting the same formula in data validation, flips the responses. https://docs.google.com/spreadsheets/d/1Bz4ov4aRkz49TRKD0xFkA5chFscgiimu4jLxtoagCqQ/edit?usp=sharing – Paul Marvell Jun 08 '21 at 15:53
Yeah, I think you may be better off using your helper column to tell you when the validation fails. What about changing the data validation criteria to be a Number greater than 0? – Aresvik Jun 08 '21 at 16:14

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

2 Answers2