This post will guide you how to remove duplicate characters from a text string in one cell in Excel. How do I remove duplicate words from a cell with a User Defined Function in Excel 2013/2016.

• Remove Duplicate Characters
• Remove Duplicate Words

Remove Duplicate Characters

---

Assuming that you have a list of data in range B1:B2, in which contain some test string values. And you want to remove all duplicate characters from text string in one cell. For example, the text string is aabbcc, removing duplicate characters to get the last string as abc. How to do it. You can use a User Defined Function to achieve the result in Excel. Do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Function removeDupes(str As String) As String
Dim i As Long
Dim cntUnique As Long
Dim objDict As Object

    Set objDict = CreateObject("Scripting.Dictionary")
    'objDict.CompareMode = 1 'Use 1 for Text Compare - which means upper/lower case treated the same - uncomment this for case sensitivity
    
    For i = 1 To Len(str)
        If objDict.Exists(Mid(str, i, 1)) Then
            'do nothing
        Else
            objDict.Add Mid(str, i, 1), cntUnique
            removeDupes = removeDupes & Mid(str, i, 1)
            cntUnique = cntUnique + 1
        End If
    Next i
        
    objDict.RemoveAll
    Set objDict = Nothing
End Function

#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=removeDupes(B1)

You would notice that all duplicate characters have been removed from the given cell.

Remove Duplicate Words

---

Assuming that you have a list of data in range B1:B2,  in which contain text string values. And you want to remove all duplicate words from a given cell in Excel. How to do it.  You can use another User Defined Function to achive the result. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Function NoDupWords(InS As String, Sep As String) As String
Dim SS() As String
Dim N As Long
Dim M As Long
Dim B As Boolean
Dim ResultS As String
SS = Split(InS, Sep)
For N = LBound(SS) To UBound(SS)
    B = False
    For M = N + 1 To UBound(SS)
        If StrComp(SS(N), SS(M), vbTextCompare) = 0 Then
            B = True
        End If
    Next M
    If B = False Then
        ResultS = ResultS & " " & SS(N)
    End If
Next N
NoDupWords = Trim(ResultS)
End Function

#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=NoDupWords(B1,”,”)

You would notice that all duplicate words have been removed from the given cell.