0

I'm trying to solve this issue:

How to use the interval quantifier curlybraces "{}" from 0 up to 4 in a REGEXMATCH Google Sheets formula to make it match only the occurrences from zero occurrence up to 4 occurrences and no more?

Here the source and context I started first from (section Quantifiers — * + ? and {}) Regex tutorial — A quick cheatsheet by examples

Specifying the following:

a(bc){2,5}  matches a string that has a followed by 2 up to 5 copies of the sequence bc

My formula is:

=REGEXMATCH($A7,"a(bc){0,4}")

Here the 1st input in A7:

abcbcbcbcbc

Contrary to expectation, it returns TRUE despite A7 having more than 4 bc's as input in A7.

The same contrary to expectation result occurs for the following intervals {1,4} and {2,4} in :

=REGEXMATCH($A7,"a(bc){1,4}")

=REGEXMATCH($A7,"a(bc){2,4}")

It still returns matches despite 5 occurences of bc's sequences in those latter two cases as well.

Here the Sheet: quantifier interval in regex from zero to defined interval end

I read the general regex info answer here Learning Regular Expressions [closed] but couldn't find the solution.

How to make it return FALSE for any input of more than 4 bc's in A7?

Thanks a lot for your help!

Lod
  • 657
  • 1
  • 9
  • 30

1 Answers1

2

A regex does not have to match the entire string you are checking it against by default. The function will return True if the regex matches any substring of the provided string.

To change that behaviour add the character ^ to match the start of the subject string and the character $ to match its end.

For example: =REGEXMATCH($A7,"^a(bc){0,4}$") will not match abcbcbcbcbc.

theblackips
  • 779
  • 4
  • 16
  • Thanks @theblackips for the explanation, makes sense and it works great! Take care! – Lod Jun 30 '19 at 12:55