Hello,
As print screen below, I have some text (A1:A3) that I'd like to split.
The delimiter are ( , / ; | : ).
I was able to split it using FILTERXML and SUBSTITUTE (as shown in column E to K depending on the number of text). However, when I'd like it to be return the result dynamically both horizontally and vertically, I get #CALC! error.
Question now is how do I incorporate my dynamic array result horizontally down vertically based on the size of my data.
My working formula:
"=TRANSPOSE(FILTERXML(""<M><D>""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"","",""||""),""/"",""||""),"";"",""||""),"":"",""||""),""||"",""</D><D>"")&""</D></M>"",""//D""))"
Not working formula:
=BYROW(A1:A3,
LAMBDA(br, TRANSPOSE(FILTERXML("<M><D>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(br,",","||"),"/","||"),";","||"),":","||"),"||","</D><D>")&"</D></M>","//D")))
)
SplitText Dynamically.png
Bookmarks