Search code examples
regexsplitgoogle-sheetsformulas

trying to split within arrayformula inside google spreadsheet

I am trying to use the split function within the ARRAYFORMULA function inside a google spreadsheet.

I want "1.2.3.4" to become "1", "2", "3", "4"

This is the code for a one-liner (working for 1 row only):

=SPLIT(A2;".")

This is what I want to achieve (to work on multiple rows at the same time):

//alone
=SPLIT(A2:A;".")

//or with ARRAYFORMULA
=ARRAYFORMULA(SPLIT(A2:A;"."))

Since the SPLIT function can't be used within the ARRAYFORMULA function, I searched for a workaround (ARRAYFORMULA() does not work with SPLIT()):

=ARRAYFORMULA(IFERROR(REGEXEXTRACT("."&A2:A;"^"&REPT(".+[^.]+";COLUMN(OFFSET(A2;;;1;4))-1)&".+([^.]+)")))

It's almost working except for the fact that it doesnt split correctly, here is the result I get:

"1.2.3.4" becomes "4", "4", "4", "4"

If I can get the workaround to actually work correctly or a better alternative that would be more than awesome...

Solution

  • Your expression creates regular expressions like this:

    ^.+[^.]+.+[^.]+.+[^.]+.+([^.]+)

    The .+ part will match anything, as much as possible, thus pushing he last ([^.]+) to the end.

    If you use an expression like this instead, it should work better:

    ^\.[^.]*\.[^.]*\.[^.]*\.([^.]*)

    It will always count each ., and never push the last group too far.

    (You could use {3} instead of repeating the subpattern, but that would change your original formula too much)

    Here is the corresponding formula:

    =ARRAYFORMULA(IFERROR(REGEXEXTRACT("."&A2:A;
    "^"&REPT("\.[^.]*";COLUMN(OFFSET(A2;;;1;4))-1)&"\.([^.]*)")))