0

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.

1

Use regular expressions and REGEXMATCH

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

0
  • taking it on next level it would be:

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

    1

    ...where requirements for TRUE outcome are:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.