How to split texts from dynamic range?

Question

I have a long dataset downloaded from ERP system. They are pipe | delimited. I have to split then to individual column. I can use FILTERXML() or TEXTSPLIT() to split them to columns. I was trying to use Textsplit function dynamically so that if any new data comes to end row, it automatically split to columns. Below is my sample data (each line in a single cell).

HANG TAG (FG00028 NEXT||||(69 X 18)mm|||U LABEL|||||1631/2022|||||||||)             
BOX END LABEL (FG00781 NEXT||||(114 X 68)mm|||NEXT-BK|||||1804/22|||||||||)             
HANGER STICKER (FG00840 NEXT||||(40 X 40)mm|||WWL251|||||1616/22|||||||||)              
HANGER STICKER (FG00840 NEXT||||(34 X 17) mm|||WWL251|||||1621/2022|||||||||)               
CARE LABEL (FG00722 NEXT|CO-069593[QTY:2248]PER:0.35%|||(130X 25)mm|||NEXT-NF|||||1573/22|||||||||)             
CARE LABEL (FG00722 NEXT||||(130X 25)mm|||SWS-COM|||||1578/2022|||||||||)               
CASCADE CARD (FG00780 GEORGE|1078230-31-28-29|||(601 X 276.5) mm|||MUPC2||LIZ|||1639/22|||||||||)               
CARE LABEL (FG00722 NEXT||||(130X 25)mm|||SWS-SIM|||||1573/22|||||||||)             
CARE LABEL (FG00722 GEORGE|PO-1077981|||(20X70)mm|||CLGW|||||1734/2022|||||||||)                
BOX END LABEL (FG00781 NEXT||||(65X 105)mm|||BK|||||1177/22|||||||||)               
WOVEN MAIN LABEL (FG00806 GEORGE|PO-1084217 ERPNO-22S23P111037/1|||10X77MM|||GCBMF|||||1752/2022|||||||||)              
OVER RIDER (FG00826 Sainsbury|PP sample for developing|||31X95MM|||TU-DENOV-L2|||||365/22|||||||||)             
DISCLAIMER TAG (FG00829 SAINSBURY|2523229/141048665||||||TU-DISCSW24|||||1571/22|||||||||)              
HANGER STICKER (FG00840 GEORGE|1071004-1070769-70-1070764-65-66-67-1071006-1070776|||37X24MM|||MLH|||||1462/2022|||||||||)              
DISCLAIMER TAG (FG00829 SAINSBURY|2523238/1410980784||||||TU-DISCSW24|||||1572/22|||||||||)

So far I can apply TEXTSPLIT() with TEXTJOIN() to split dynamically like-

=TEXTSPLIT(TEXTJOIN("#",TRUE,A1:A15),"|","#")

Above formula give me desired result but I can't use this for long dataset as TEXTJOIN() has character limit. So, how can I split text from dynamic range by excel built-in function?

As long as you have a fixed number of result columns, you could use something like: `=MAKEARRAY(ROWS(A1:A15),22,LAMBDA(r,c,INDEX(TEXTSPLIT(INDEX(A1:A15,r),"|"),1,c)))`? You could add in LET so there's only one range reference to change, or use a named range. — Rory, Sep 26 '22 at 10:50
@Rory It works on sample data. I will apply to long dataset and feedback. — Harun24hr, Sep 26 '22 at 11:00
@Terio `BYROW` will only return a single result per row. The OP wishes to have several results per row. — Jos Woolley, Sep 26 '22 at 12:59

JvdV · Accepted Answer · 2022-10-21T09:06:23.747

8

Have a go with TEXTSPLIT() nested in REDUCE():

Formula in B1:

=IFERROR(DROP(REDUCE(0,A1:A15,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{"|"," ("})))),1,-1),"")

edited Oct 21 '22 at 09:06

answered Sep 26 '22 at 11:11

JvdV

70,606
8
39
70

1

It works for me but really slow for big dataset. I have records over 1000000. Anyway learn something new. Thank you. – Harun24hr Sep 27 '22 at 08:05
@Harun24hr maybe a pq based solution would be a better way to go if speed is suboptimal – JvdV Sep 27 '22 at 08:09
I have done it by text to column method to my real case for this time. – Harun24hr Sep 27 '22 at 08:24
1

@Harun24hr, I updated as I just learned about a cool new trick to `VSTACK()` in `REDUCE()`. – JvdV Oct 21 '22 at 09:02
Let me try this one. – Harun24hr Oct 21 '22 at 09:49

How to split texts from dynamic range?

1 Answers1

Linked