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