I'm very new to the regex scene so forgive my level of intelligence! I want a single cell to be 5 or 7 characters long via data validation and an error message for a false return.

I've come up with this for cell A2:

=REGEXMATCH(A2&"","^(.){5}$") --- only 5 characters

=REGEXMATCH(A2&"","^(.){5,7}$") ---between 5 and 7 characters

Can't figure out how to do 5 or 7 in a single REGEXMATCH.


Use len(), like this:

=or( len(A2) = 5, len(A2) = 7) )

If you additionally need to ensure the value is a number, add isnumber(), like this:

=or( len(A2) = 5, len(A2) = 7) ) * isnumber(A2)

Only add a regexmatch() to the formula if you need to check for the presence of a certain digit/letter sequence or some such pattern.

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.