Fix for ARRAYFORMULA() SPLIT()

Question

I have cells with data like this (all in the same cell)

Lender: ASB, Limit: 45345, Balance: 34534
Lender: BNZ, Limit: 5435, Balance: 345

This formula achieves the outcome that I am after; it separates the data by comma and then by line into one row:

=SPLIT(join(",",SPLIT(substitute(A2," ",""),Char(10))),",")

However, I need this to work as an array, something like:

=ArrayFormula(if(A2:A="","",SPLIT(join(",",SPLIT(substitute(A2:A," ",""),Char(10))),",")))

I have read this ARRAYFORMULA() does not work with SPLIT() and can't come up with a working solution. Any ideas?

Adam's formula works fine. All you needed is to replace Char(10): `=ArrayFormula(IFERROR(REGEXEXTRACT(","&SUBSTITUTE(A1:A,char(10),","),"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")))` — Max Makhrov, Apr 13 '16 at 14:32

score 0 · Accepted Answer · answered Apr 13 '16 at 14:24

0

You can use this (pretending your original data is in column B):

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(","&REGEXREPLACE(B2:B,"\n",","),"^"&REPT("\,[^,]*",COLUMN(OFFSET(A1,,,1,6))-1)&"\,([^,]*)")))

answered Apr 13 '16 at 14:24

Aurielle Perlmann

Genius!! Thanks so much! – Fred Brown Apr 14 '16 at 21:32
No problem! Btw If my answer solved your problem can you please accept the answer when you get a chance? – Aurielle Perlmann Apr 14 '16 at 21:57
Ahhh right. Sorry. New to this. Thanks so much for your time :D – Fred Brown Apr 16 '16 at 00:25

1 Answers1