dynamically split cell into separate cells

[Danny54]

I'm looking for a way to split comma delimited data (Tags) into separate cells.
new sheet shows the data from Tags being split by the comma and inserted into a new cell by itself having the data aligned for that row.


it would be great if headers were inserted for the cols inserted as shown in new sheet 1

Thanks

Date1

Date2

Date3

value

%

Owner

Installer

color

local

zip

zone

city

sys

Tags

Notes

ID

Ex

DOW

Im

CID

2022-08-23T05:39:20Z

2021-05-27T00:49:25Z

2022-08-23T05:12:36Z

0

10

John

John

no

florida,kansas,new york,texas

60054

2022-08-17T05:43:43Z

2021-07-14T10:54:56Z

2022-08-17T04:17:17Z

280

10

Joe

Joe

no

ohio,kansas,texas

87540

new sheet

Date1

Date2

Date3

value

%

Owner

Installer

color

local

zip

zone

city

sys

Tags

Notes

ID

Ex

DOW

Im

CID

2022-08-23T05:39:20Z

2021-05-27T00:49:25Z

2022-08-23T05:12:36Z

0

10

John

John

no

florida,kansas,new york,texas

60054

florida

kansas

new york

texas

2022-08-17T05:43:43Z

2021-07-14T10:54:56Z

2022-08-17T04:17:17Z

280

10

Joe

Joe

no

ohio,kansas,texas

87540

kansas

ohio

texas

new sheet 1

Date1

Date2

Date3

value

%

Owner

Installer

color

local

zip

zone

city

sys

Tags

Notes

ID

Ex

DOW

Im

CID

florida

kansas

new york

ohio

texas

2022-08-23T05:39:20Z

2021-05-27T00:49:25Z

2022-08-23T05:12:36Z

0

10

John

John

no

florida,kansas,new york,texas

60054

florida

kansas

new york

texas

2022-08-17T05:43:43Z

2021-07-14T10:54:56Z

2022-08-17T04:17:17Z

280

10

Joe

Joe

no

ohio,kansas,texas

87540

kansas

ohio

texas

 

[Fluff]

Both textjoin & filterxml have a limit of ~32,700 characters, so you are possibly hitting that limit, but I wouldn't have thought so unless you have long strings in those cells.

 

[JamesCanale]

TEXTJOIN documtation says that you can only have 252 text elements, and only 32k characters.

 

[Fluff]

If you just put

=TEXTJOIN(",",TRUE,A2:A150)

into a cell do you get the #value error?

 

[Danny54]

Thanks Fluff / James

That was my original value used this morning when it stopped with the error at row 64.
I think James may have hit it, there is a max amount of rows that can be read in using the TEXTJOIN function
At line 64 we have 111 different splits with a average length of text between the comma separated value is 55.

Any other suggesting on resolving this task?

Perhaps using Power Query...

 

[Fluff]

there is a max amount of rows that can be read in using the TEXTJOIN function

That is not true, the number of rows is unlimited, just the number of characters. Can you answer my question in post#13?

 

[Danny54]

Sorry,
Yes, I get a value error

Thanks

 

[Fluff]

In that case the string is too long, do you have the HSTACK function yet?

 

[Danny54]

im running the latest version of excel and office products

 

[Fluff]

If you type =Hs into a cell do you get the HSTACK option, it's a very new function & not everyone has it yet.

 

[Fluff]

If you do have HSTACK use the formula in D2, otherwise use the one in D3

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Hollingdean,and,Stanmer			Hollingdean	and	Stanmer	Burwell	Bramley	Stanningley	Cupernham	Coton	Lea	Bridge	Hertford	Castle
3	Burwell			Hollingdean	and	Stanmer	Burwell	Bramley	Stanningley	Cupernham	Coton	Lea	Bridge	Hertford	Castle
4	Bramley,and,Stanningley
5	Cupernham
6	Coton
7	Lea,Bridge
8	Hertford,Castle
9
Report

Cell Formulas
Range	Formula
D2:O2	D2	=DROP(REDUCE("",A2:A8,LAMBDA(a,b,UNIQUE(HSTACK(a,TEXTSPLIT(b,",")),1))),,1)
D3:O3	D3	=REDUCE(LEFT(A2,FIND(",",A2)-1),A2:A8,LAMBDA(a,b,LET(c,COLUMNS(a),z,TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(b,",","</m><m>")&"</m></k>","//m")),s,SEQUENCE(,c+COLUMNS(z)),UNIQUE(IF(s<=c,a,INDEX(z,,s-c)),1))))
Dynamic array formulas.