How to remove duplicate comma separated values within a cell

The following macro will help you remove the duplicates and give you a clean string.

Function stringOfUniques(inputString As String) As String

Dim inArray() As String

Dim xVal As Variant

Dim s As String

inArray = Split(inputString, “,”)

For Each xVal In inArray

If InStr(s, Trim(xVal)) = 0 Then _

s = s & Trim(xVal) & “,”

Next xVal

stringOfUniques = s

End Function

Step 1: To insert a macro, If you are using a Mac, click fn+alt+F11, This is how you can open a macro screen,

Step 2:  Paste the above macro into the macro screen and save it.

Step 3: Now choose the column in which you want to apply the formula and type in =stringOfuniques, Automatically the function we defined will pop up.

Step 4: After typing the function name, click on the input column you wanted to remove the duplicates on.

Step 5: Now you can see the desired output.

Leave a Comment