dynamically split cell into separate cells

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
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

Date1Date2Date3value%OwnerInstallercolorlocalzipzonecitysysTagsNotesIDExDOWImCID
2022-08-23T05:39:20Z2021-05-27T00:49:25Z2022-08-23T05:12:36Z010JohnJohnnoflorida,kansas,new york,texas60054
2022-08-17T05:43:43Z2021-07-14T10:54:56Z2022-08-17T04:17:17Z28010JoeJoenoohio,kansas,texas87540

new sheet
Date1Date2Date3value%OwnerInstallercolorlocalzipzonecitysysTagsNotesIDExDOWImCID
2022-08-23T05:39:20Z2021-05-27T00:49:25Z2022-08-23T05:12:36Z010JohnJohnnoflorida,kansas,new york,texas60054floridakansasnew yorktexas
2022-08-17T05:43:43Z2021-07-14T10:54:56Z2022-08-17T04:17:17Z28010JoeJoenoohio,kansas,texas87540kansasohiotexas

new sheet 1
Date1Date2Date3value%OwnerInstallercolorlocalzipzonecitysysTagsNotesIDExDOWImCIDfloridakansasnew yorkohiotexas
2022-08-23T05:39:20Z2021-05-27T00:49:25Z2022-08-23T05:12:36Z010JohnJohnnoflorida,kansas,new york,texas60054floridakansasnew yorktexas
2022-08-17T05:43:43Z2021-07-14T10:54:56Z2022-08-17T04:17:17Z28010JoeJoenoohio,kansas,texas87540kansasohiotexas
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The filterxml works, but TEXTSPLIT is coming to 365.

MrExcelPlayground11.xlsx
ABCDEFGH
1TagsNotesThingfloridakansasnew yorktexasohio
2florida,kansas,new york,texasfloridakansasnew yorktexas 
3ohio,kansas,texas kansas texasohio
Sheet16
Cell Formulas
RangeFormula
D1:H1D1=TRANSPOSE(UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A6),",","</y><y>")&"</y></x>","//y")))
D2:H3D2=IF(ISNUMBER(SEARCH(D$1,$A2)),D$1,"")
Dynamic array formulas.
 
Upvote 0
Thanks James

following your example I get this as a result

TagsNotesThingflorida#VALUE!#VALUE!florida#VALUE!
florida,kansas,new york,texasfloridaflorida
ohio,kansas,texas
 
Upvote 0
Any chance you can use xl2bb - I can't see what is going on with yours.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Again,

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

:)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top