CODEWITHSUNDEEP
regexgoogle-sheetsarray-formulasgoogle-sheets-formula
Wicket
Wicket

Reputation: 38219

Why is REGEXEXTRACT returning a single value when it is expected to return an array of 1 row and multiple columns?

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

Upvotes: 2

Views: 2836

Answers (2)

Mariano
Mariano

Reputation: 6511

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.

Upvotes: 4

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

The reason your not seeing expected results in your particular formula, is the order in which you are using arrayformula and regexextract - you need to either modify your regex syntax to extract 2 groups, or you need to make an array to separate each regex extract function.

There are a few ways to do it, 1 way is to create a literal array and just specify the 2 extract patterns:

={REGEXEXTRACT(A1,"^."),REGEXEXTRACT(A1,"^.{2}")}

enter image description here

The other is to create 2 capture groups, only thing about the second one is by default is comes back in reversed order, but you can easily swap that by sort():

=REGEXEXTRACT(A1,"^((.).)")

enter image description here

Upvotes: 3

Related Questions