How do I remove duplicate values in a cell seperated with a /?

Question

I have multiple cells in excel that have as follows:

    b1= E4I8/E4I8/E4I8/E4I8
    b2=D3B2/B30C1/D3B2/D3B2/D3B2/B30C1


    multiple /xxxx/ 

How do I remove these duplicate text strings in the same cell?

Thank you

Solution

This function uses the keys of a dictionary to build a unique list of parts from a passed-in string (add a reference to Microsoft Scripting Runtime):

Public Function UniqueParts(separator As String, toParse As String) As String
    Dim d As New Scripting.Dictionary, part As Variant, i As Integer
    For Each part In Split(toParse, separator)
        d(part) = 1
    Next
    UniqueParts = Join(d.Keys, "/")
End Function

You can use this function in an Excel formula:

=UniqueParts("/","E4I8/E4I8/E4I8/E4I8")

or with a cell reference:

=UniqueParts("/",B2)

You can also use this inside a macro that iterates over a range of cells.

Comments

[Kazimierz Jawor]

Thanks, in my national Excel we have ; where you have ,... but I changed into international standards :)

[chris neilsen]

Good attempt, but parts isn't declared, isn't really used, and isn't needed. Delete it and this will be a +1.

[Zev Spitz]

@chrisneilsen I was initially returning an array.