How regex changed my life (for good) – always learning – match exact number of letters/numbers

On April 21st, 2022 in Tech

I was working today on a Google Spreadsheet with more than 5,000 lines and I had to filter some columns. Google already have a pretty good filter by value, but you can also use conditions or custom functions.

I was looking for a way to filter every cell that doesn’t begins with exact 2 numbers. That one was pretty simple and fast (please note that I use Google Spreadsheets with my locale in Brazil, so you may have to adapt the function):

=NO(REGEXMATCH(B2;"^[0-9]{2}"))
Code language: JavaScript (javascript)

Next, I would like to filter other column for avoiding cells that begins with “+”, AND is not blank, AND doesn’t contains 8 or 9 numbers. The 1st and 2nd rules were easy, but I had some trouble with the 3rd.

Searching on the internet, I found this Stackoverflow post that solved my problem. This is my function:

=NOT(OR(REGEXMATCH(AO2;"^\+");REGEXMATCH(AO2;"^(?:[^0-9]*[0-9]){8,9}[^0-9]*$");ISBLANK(AO2)))
Code language: JavaScript (javascript)

What I learned today was how to use (?:…). “(?:…) is a non-capturing group that allows quantifying a sequence of subpatterns and is thus easily adjustable to match 3, 4 or more specific sequences in a string” as Mr. Wiktor Stribiżew explained on his answer.

I had already used (?:…) for the purpose of only non-capturing group sometime ago, but for “quantifying a sequence of subpatterns and is thus easily adjustable to match 3, 4 or more specific sequences” was new to me.

You can try his demo at https://regex101.com/r/DyP5e7/1.

I hope it may help somebody else too.

Tags: regex