Data Validation in Google Sheets Using Multiple Criteria

Question

I have a column with text in the format:

[text:more text/numbers]

I want to ensure whatever is entered into the cell conforms to this syntax, or at least as much of it as possible. text is always 5 characters long and capitalized. The part after the colon is of variable length.

Currently, I am using simple data validation to check that the trailing ] is in the cell. The criteria are Text contains ], rejecting input for everything without the ].

Using an IF statement, could I check the cell for those symbols/string lengths and reject everything that doesn't conform? Ideally, I would use the formula within the data validation feature, rather than as a script, for example:

Criteria: Custom formula is =(FORMULA HERE)

Any ideas would be appreciated on this.

Answer 1

Use regular expressions and REGEXMATCH

I think that =REGEXMATCH(A1,"^\[.*]$") should work.

Answer 2

• taking it on next level it would be:

  =REGEXMATCH(A2, "^\[.{5}:.*/\d+]$")

  

  ...where requirements for TRUE outcome are:

  • starting with [
  • 5 characters before :
  • any length characters between : and /
  • numbers after /
  • ending with ]