Split and search for values in cell

[Danny54]

Good Morning

I wondering if its possible to create a formula that would split a cell by a comma and keep only the values that start with "TV -".
Keep only unique values
Take the data and insert a line feed between each grouped value before saving it to the column as shown in the Output column below)

The data looks like this

Group

TV - Blue, TV - Blue, TV - Red, Parts, Wheels

The desired output would look like this

Group	Output
TV - Blue, TV - Blue, TV - Red, Parts, Wheels	TV - Blue, TV - Red

 

[alansidman]

With Power Query, aka Get and Transform Data and found on the Data Tab of the Ribbon

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Group", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Group"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Group] <> " Parts" and [Group] <> " Wheels")),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Group", Text.Trim, type text}})
in
    #"Trimmed Text"

 

[Fluff]

How about

=LET(a,TRIM(TEXTSPLIT(A2,,",")),TEXTJOIN(CHAR(10),,UNIQUE(FILTER(a,LEFT(a,4)="tv -"))))

 

[Danny54]

=LET(a,TRIM(TEXTSPLIT(A2,,",")),TEXTJOIN(CHAR(10),,UNIQUE(FILTER(a,LEFT(a,4)="tv -"))))

When I insert this formula into my cell, I get the following

TV - BlueTV - Red

all on the same line. Do I need to have the cell formatted specifically so the data is split like

TV - Blue
TV - Red

Thanks

 

[Fluff]

You need to format the cell to Wrap Rows.

 

[Danny54]

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.