The matrix management feature in Google Spreadsheets is convenient and combined with functions that handle regular expressions like REGEXMATCH and REGEXEXTRACT, among others, makes it especially useful.
I have a case in which I do not know what is due that does not give the expected result. Here's what I'm trying to do:
Spreadsheet Settings
Regional Settings: Mexico, use . (dot) as decimal separator.
Entry
A1: abcde
Formula
B1: =ArrayFormula(REGEXEXTRACT(A1,{".{1}",".{2}"}))
Expected result
B1: a
B2: ab
Obtained result
B1: a
B2:
Known workaround
=ArrayFormula(TRANSPOSE(REGEXEXTRACT(A1,{".{1}";".{2}"})))
This question also has being posted on the Spanish site -> https://es.stackoverflow.com/q/55704/65
Quoting Jean-Pierre Verhulst on a similar case in REGEXEXTRACT Array Mysteriously Stopped Working Today (Google Docs Help Forum):
The team is well aware of the issue and a fix should be there soon.
Coincidentally, it was published Jan 4th, 2017, the same date AdamL modified his answer to ARRAYFORMULA() does not work with SPLIT(), explaining that:
REGEXEXTRACT no longer appears to support an array for the second argument.
We can conclude that this behaviour in ArrayFormula is due to a modification in Google Sheets, allowing SPLIT in array formulas, with the consequence of REGEXEXTRACT not accepting multiple columns as input in the regex.
This is probably because REGEXEXTRACT, with multiple capture groups in the regular expression, yields an horizontal array, one cell for each group. Having an array as argument, the behaviour may be undefined, but that is plain argumentative on my side.