Home / Questions / 55704
Accepted
Rubén
Asked: 2020-03-16 21:07:51 +0800 CST 2020-03-16 21:07:51 +0800 CST

Why does REGEXEXTRACT return a single value when it is expected to return a 1-row, multi-column array?

  • 772

The array handling feature of 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 where I don't know why it doesn't give the expected result. Here's what I'm trying to do:

Spreadsheet
Settings Regional Settings: Mexico (use . as decimal separator)

Input
A1:abcde

Formula
B1:=ArrayFormula(REGEXEXTRACT(A1,{".{1}",".{2}"}))

Expected result
B1: a
B2:ab

Result obtained
B1: a
B2:

Known workaround
=ArrayFormula(TRANSPOSE(REGEXEXTRACT(A1,{".{1}";".{2}"})))

This question has also been posted on the English site -> https://stackoverflow.com/q/42826011/1595451

regex

2 Answers

  1. It seems to be a bug.

    Although the return type is not explicitly documented in the REGEXREXTRACT documentation , it should work with Arrays .

    In fact in the image we can see that it ;works, but ,not:

    Examples of functions and their results

    Another workaround is to create your own array; ie:{regexextract(A1, ".{1}"), regexextract(A1,".{2}")}

    Cheers!

    • 2
  2. Best Answer

    As expressed by Jean-Pierre Verhulst in an analogous case in REGEXEXTRACT Array Mysteriously Stopped Working Today (Google Docs Help Forum) :

    The team is aware of this case and it should be fixed soon.


    Coincidentally, the post date is 4/1/2017, the same date that AdamL modified his answer on ARRAYFORMULA() does not work with SPLIT() , explaining that:

    REGEXEXTRACT seems to have stopped supporting an array in the second parameter.


    We can conclude that it is due to a change in the behavior of ArrayFormula , in which the code was improved to allow the use of functions like SPLIT with functions on arrays, but left REGEXEXTRACT without accepting specific cases. This is the case particularly with ranges that extend horizontally.

    This is probably because REGEXEXTRACT can result in a variable range of horizontal cells. When capturing groups are used in the regular expression, the function returns one cell for each group, and having an array as an argument, the behavior it should have is not defined. However, the latter is entirely speculative on my part.

    • 2