Find duplicate values and delete them in a single cell

[Amirah]

Hi guys

I'm trying to find duplicate values (precisely duplicate email) in a single cell. I've got numerous of data that need to be clean by removing duplicates in a single cell. Then, i want it to be deleted directly. Appreciate if there is a formula/steps that can be use to solve this problem. Macro is possible but I'm focusing on other approach right now.

 

[Fluff]

What version of Excel are you using & on what platform?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

If the lower table solution works for you then Power Query can do this.

Book3
	A
1	Email
2	ABC123@gmail.com; ABC123@gmail.com
3	Qweer.098@yahoo.com; Poiu.123@gmail.com ;Poiu.123@gmail.com; Zxc.098 @gmail.com
4
5
6	Email
7	ABC123@gmail.com
8	Qweer.098@yahoo.com
9	Poiu.123@gmail.com
10	Zxc.098 @gmail.com
Sheet2

the Mcode to make this happen follows. In PQ, you can split your data and have it go to rows instead of columns. Then you can delete the duplicates.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Email", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Email"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Email", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"