0

enter image description here

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?

J. SungHoon
  • 201
  • 1
  • 5
  • It is returning an array because you defined it as an arrayformula. Therefore it is in fact expanding down in the column but also across as many rows as you have a value to check for (G2:I2). What is it you're looking for? – Kevin P. Feb 24 '22 at 14:59
  • @KevinP. I was expecting `REGEXEXTRACT` to do just as `REGEXMATCH` and `REGEXREPLACE` filled out rows G,H,I. However, `REGEXEXTRACT` only fills row G. – J. SungHoon Feb 24 '22 at 15:10
  • Oh, I understand. I don't believe REGEXEXTRACT supports an array as the second parameter. Whether this is a bug or not take a look at this: https://stackoverflow.com/a/42863545/7502029 – Kevin P. Feb 24 '22 at 15:21

1 Answers1

1

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)})

Kevin P.
  • 907
  • 7
  • 18
  • Hmm... that's a bit disappointing. But I'm glad `REGEXEXTRACT` expand to column direction in `ARRAYFORMULA`. I think the formula in UPDATE section in your answer is an optimal option that reflects this characteristics well. Editing formulas in situations where the number of columns is large or changing is tedious, so using Apps Script to create/update formula to suit your needs seems to be the best way. – J. SungHoon Feb 24 '22 at 15:44
  • Yes you could make the formula dynamic with Apps Script, or if your ranges are written per column you could only show the results in non empty cells: `=ARRAYFORMULA(IF($F2:$F<>"",{REGEXEXTRACT($F2:$F,G$2),REGEXEXTRACT($F2:$F,H$2),REGEXEXTRACT($F2:$F,I$2)})` This removes the lower boundary and checks all cells from F2 and down returning results for cells that are not empty. – Kevin P. Feb 24 '22 at 16:02
  • Side note, I just tested the REGEXEXTRACT formula with JOIN and it seemed to work as long as your conditions will only be one REGEX condition. If multiple conditions are met it will only return the first condition starting from left to right. `=ARRAYFORMULA(REGEXMATCH($F4:$F10,JOIN("|",G$2:I$2)))` This tells me that REGEXEXTRACT can in fact accept an array as the second parameter. Anyways, you have some options now. :) – Kevin P. Feb 24 '22 at 16:40