Alternative Formulas of CLEAN

[Dhinakaran]

Hello,

I've a data in my sheet here few of the cells contains few special characters which i need to remove/replace with null. Did try with CLEAN while it didn't work.

Below are the cell contents/samples where ðŸˆâ€ resembles kinda emojis. 💧HYDRATING FOOD: The gravy format and moisture content in this kitten wet cat food support hydration
ðŸˆâ€HEALTH BENEFITS: It supports a healthy immune system. Vitamins & minerals for an effective body system. DHA and Omega 3 fatty acids support healthy brain and vision development.
💧HYDRATING FOOD: The gravy format and moisture content in this kitten wet cat food support hydration. ✅QUALITY INGREDIENTS: Whiskas wet cat food is made with real fish, and it is easy to eat and digest.
ðŸŸDELICIOUS FLAVOUR: This cat food contains delicious mackeral flavour and an appealing aroma. 😺COMPLETE AND BALANCED CAT FOOD: Whiskas Wet Cat Food provides 100% complete and balanced nutrition for kittens (2-12 months).

Appreciate your inputs.


TIA,

 

[arthurbr]

Power Query will do the job

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each let splitColumn11 = Splitter.SplitTextByDelimiter("’", QuoteStyle.None)([Column1.1]), splitColumn112 = List.Reverse(Splitter.SplitTextByDelimiter("’", QuoteStyle.None)([Column1.1])) in Text.Combine({Text.Middle(splitColumn11{1}?, 1, 3), Text.Middle(splitColumn11{1}?, 6, 1), Text.Middle(splitColumn11{1}?, 4, 1), Text.Middle(splitColumn112{0}?, 5)}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Column1.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column1.2"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Reordered Columns", "Merged", each Text.Combine({[Custom], [Column1.2]}, ":"), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom", "Column1.2"})
in
    #"Removed Columns1"