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

 

[JamesCanale]

The filterxml works, but TEXTSPLIT is coming to 365.

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H
1	Tags	Notes	Thing	florida	kansas	new york	texas	ohio
2	florida,kansas,new york,texas			florida	kansas	new york	texas
3	ohio,kansas,texas				kansas		texas	ohio
Sheet16

Cell Formulas
Range	Formula
D1:H1	D1	=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A6),",","</y><y>")&"</y></x>","//y")))
D2:H3	D2	=IF(ISNUMBER(SEARCH(D$1,$A2)),D$1,"")
Dynamic array formulas.

 

[Danny54]

Thanks James

following your example I get this as a result

Tags	Notes	Thing	florida	#VALUE!	#VALUE!	florida	#VALUE!
florida,kansas,new york,texas			florida			florida
ohio,kansas,texas

 

[JamesCanale]

Any chance you can use xl2bb - I can't see what is going on with yours.

 

[Fluff]

Did you confirm the formula with Ctrl Shift Enter & then drag across?
If so don't, just put it in one cell & it will spill across.

 

[Danny54]

Thanks
Yes, thats what I was doing. I just cut and pasted the first formula which filled up the headers.
then I pasted the second formula with Ctrl Shift Enter, then drag across

all is working

Thanks

 

[Danny54]

James can you loosely explain how this formula works so I can learn from it?

=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A6),",","</y><y>")&"</y></x>","//y")))

Thanks

 

[JamesCanale]

The textjoin puts all of the lists in column a together into one set of comma delimited places.
The filterxml formula splits up the text in the textjoin by the commas. Basically, all of that <x><y> stuff creates an xml with cell dividers, and the filterxml makes it into a spilling array. The "," part is the delimiter from the text join statement. It's funny and sloppy looking, and 365 will have the TEXTSPLIT function soon that will do that in a more readable and elegant way. For now, it's super helpful though.
Then unique takes the unique values and transpose turns it from vertical to horizontal.
Basically:
1. put all of the place names together from column A into one place
2. split them all up by the commas
3. remove duplicates
4. put them in a row instead of a column.

 

[Danny54]

Again,

Thanks so much. The explanation helps me learn for future situations.

 

[Danny54]

Good Morning

I had time to implement these formulas into my production xls file this morning. When I try the following formula it throws a "#Value error" after processing row 64. (reads rows 2-150)
=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A150),",","</y><y>")&"</y></x>","//y")))

If I use this formula - success (reads only rows 2-64)
=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A64),",","</y><y>")&"</y></x>","//y")))

If I use this formula - success (reads rows 10-70)
=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A10:A70),",","</y><y>")&"</y></x>","//y")))

Im leaning towards a limit of the # of rows that can be processed to be about 64.

Is there something that I can change to read more rows. My total row count is 150

Thanks