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

 

[Danny54]

Thanks Fluff

I took your test data as you can see below and created a new sheet. Copied your formulas and pasted them

Suggestions?

Hollingdean,and,Stanmer

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

Burwell

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

#NAME?​

Bramley,and,Stanningley

Cupernham

Coton

Lea,Bridge

Hertford,Castle

 

[Fluff]

That sounds as though your Excel is not fully up to date. If you type =La into a cell do you get the LAMBDA option, or just LARGE?

 

[Danny54]

Thanks for your reply

When I type =La into a cell I get LARGE

 

[Fluff]

In that case your Excel is not fully up to date.
Is VBA an option?

 

[Danny54]

yes, i run vba macros without problems.

 

[Fluff]

Ok, how about

Sub Danny54()
   Dim Cl As Range
   Dim Txt As String
   Dim Ary As Variant
   
   For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
      Txt = Txt & "," & Cl.Value
   Next Cl
   Ary = Application.Unique(Split(Mid(Txt, 2), ","), 1)
   Range("U1").Resize(, UBound(Ary)).Value = Ary
End Sub

This will get the values from col N & put them in row 1 starting at U1

 

[Danny54]

Perfect. I ran the VBA to create the headers, then inserted James's formula to create the line items.

I also did some research on the LAMBDA function. Seems that I'm not at the Excel release level where its available yet. Looking forward to having it.

Thanks so much guys

 

[Fluff]

Glad we could help & thanks for the feedback.