Data validation using regular expressions in Google Sheets

Question

I am using the below date/time format in gSheets:

01 Apr at 11:00

I wonder whether it is possible to use Data Validation (or any other function) to report error (add the small red triangle to the corner of the cell) when the format differs in any way.

Possible values in the given format:

01 -> any number between 01-31 (but not "1", there must be the leading zero)
space
Apr -> 3 letters for month (Jan, Feb, Mar... Dec)
space
at
space
11 -> hours in 24h format (00, 01...23)
:
00 -> minutes (00, 01,...59)

Is there any way to validate that the cell contains "text/data" exactly in the above mentioned format?

Those might help other users: webapps.stackexchange.com/a/85747 webapps.stackexchange.com/questions/92665/… — Lod
– Lod, Commented Jul 21, 2023 at 12:01

RavinderSingh13 · Accepted Answer · 2022-06-16 11:37:37Z

8

The right way to do this is using Regular Expression and "regexmatch()" function in Google Sheets. For the given example, I made the below regular expression:

[0-3][0-9] (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at [0-2][0-9]\:[0-5][0-9]

Process:

Select range of cells to be validated
Go to Data > Data Validation
Under Criteria select "Own pattern is" (not sure the exact translation used in EN)
Paste: =regexmatch(to_text(K4); "[0-3][0-9] (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) at [0-2][0-9]\:[0-5][0-9]")
Make sure that instead of K4 in "to_text(K4)" there is a upper-left cell from the selected range
Save

Hope it helps someone :)

edited Jun 16, 2022 at 11:37

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

answered Apr 20, 2018 at 13:11

Jan Nieboras

1511 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Max Makhrov · Accepted Answer · 2018-04-20 14:38:47Z

1

You may try the formula for data validation:

=not(iserror(SUBSTITUTE(A1," at","")*1))*(len(A1)=15)*(right(A1,2)*1<61)

not(iserror(SUBSTITUTE(A1," at","")*1)) checks all statemant is legal date
(len(A1)=15) checks dates are entered with 2 digits
(right(A1,2)*1<61) cheks too much minutes, for some reason 01 Apr at 11:99 is a legal date..

answered Apr 20, 2018 at 14:38

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Comments

H.A. · Accepted Answer · 2022-06-16 11:41:14Z

1

Select the range of fields, where you need the data validation to occur to.
Press on -> Data -> Data validation
For "Criteria" select "Custom formula is"
Enter the following in the textfield next to "Custom formula is": =regexmatch(Tablename!B2; "^[a-z_]*$")

Where as "Tablename" should be replaced by the table name and "B2" should be replaced by the first cell of the range.

Inside the "" you enter then your regex-expression. Here this would allow only small letters and underscores.

Using the to_text() function additionally didn't work for me. So you should maybe avoid it in order to make sure, that it works.

Press save

edited Jun 16, 2022 at 11:41

answered Jun 16, 2022 at 11:34

H.A.

3786 silver badges15 bronze badges

Collectives™ on Stack Overflow

Data validation using regular expressions in Google Sheets

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related