trying to split within arrayformula inside google spreadsheet

Question

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...

Possible duplicate of [ARRAYFORMULA() does not work wirth SPLIT()](http://stackoverflow.com/questions/26137115/arrayformula-does-not-work-wirth-split) — user3347814, Jan 22 '16 at 19:36

score 5 · Accepted Answer · answered May 12 '15 at 22:54

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)&"\.([^.]*)")))

would you be able to provide example. ARRAYFORMULA(...) mentioned above doesn't work CHeers — user2886836, Jan 04 '18 at 10:57

Aurielle Perlmann · Answer 2 · 2018-06-01T21:29:15.727

-2

you could try this:

=REGEXREPLACE(A1,"\.","\"",\""")

UPDATE: Google fixed this and you can now use arrayformula with split:

=ArrayFormula(SPLIT(Sheet1!A2:A,"."))

edited Jun 01 '18 at 21:29

answered Dec 04 '15 at 08:46

Aurielle Perlmann

1

Although the proposed formula works as an array formula, for `1.2.3.4` it returns `1","2","3","4` instead of the values splited by the delimiter (dot). – Rubén Feb 22 '16 at 13:11

2 Answers2