Hello. I'm new to regex and I've been practicing on google sheets. However, I found that only REGEXEXTRACT among the 3 regex related formulas in google sheets works incorrectly in ARRAYFORMULA. The strange thing is that by ARRAYFORMULA it is expanded in the row direction, but not in the column direction. Why is this happening?

CodePudding user response：

If you are looking to return only a single column that checks for each of the conditions of regex you would have to join them together internally. Otherwise your array is expanding down and right as intended. It is checking column F against G2, then H2, then I2, therefore showing each case for each row.

=ARRAYFORMULA(REGEXMATCH($F4:$F10,JOIN("|",G$2:I$2)))

Will concatenate them in the format where the REGEX checks for each of the criteria and if ANY are true, it returns true. It essentially writes this:

=ARRAYFORMULA(REGEXMATCH($F4:$F10,"...-....-....|.com$|^!")

| - is the delimiter.

UPDATE:

I believe you're only option is to change the formatting of REGEXEXTRACT to be an array itself. Such as:

=ARRAYFORMULA({REGEXEXTRACT($F2:$F10,G$2),REGEXEXTRACT($F2:$F10,H$2),REGEXEXTRACT($F2:$F10,I$2)})