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

Aurielle Perlmann Aurielle Perlmann · Accepted Answer · 2016-04-13T14:24:47

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