CODEWITHSUNDEEP
regexsplitgoogle-sheetsformulas
user1855153
user1855153

Reputation: 579

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

Upvotes: 1

Views: 4024

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

you could try this:

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

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

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

Upvotes: -2

Markus Jarderot
Markus Jarderot

Reputation: 89171

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

Upvotes: 5

Related Questions