How to Remove Duplicate Characters or Words in String of a Cell?

This step-by-step tutorial is for you if you've ever needed to clean up a cell in your Excel spreadsheet that contained repetitive characters or words. Microsoft Excel is an effective application for handling data and provides a number of functions and methods for text manipulation.

In this tutorial, we'll show you how to use Excel's built-in functions to get rid of duplicate characters or words from a cell. This course will give you the knowledge and abilities to effectively simplify your data for better analysis or simply clean up your spreadsheet.

Remove Duplicate Characters or Words in String of a Cell

Here we will first create a user-defined function using VBA, then use the formula, and finally complete the task using the auto-fill handle. So let us see a simple process to know how you can remove duplicate characters or words in the string of a cell in Excel.

Step 1

Consider an Excel sheet where you have duplicate characters similar to the below image.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Function RemoveDupes1(pWorkRng As Range) As String
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
   xChar = VBA.Mid(xValue, i, 1)
   If xDic.Exists(xChar) Then
   Else
      xDic(xChar) = ""
      xOutValue = xOutValue & xChar
   End If
Next
RemoveDupes1 = xOutValue
End Function

Step 3

Now close the VBA using Alt + Q. Then click on an empty cell and enter the formula as =removedupes1(A2) and click enter to get the first value. Then drag down using the autofill handle.

Alt + Q > Empty Cell > Formula > Enter > Drag.

This is how you can remove duplicate characters from a string in Excel.

Note

If you want to remove duplicate values separated by commas, use the below code and use the formula =RemoveDupes2(A2,",").

Code

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
   Dim x
   With CreateObject("Scripting.Dictionary")
      .CompareMode = vbTextCompare
      For Each x In Split(txt, delim)
         If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
      Next
      If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
   End With
End Function

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove duplicate characters or words from the string of a cell in Excel to highlight a particular set of data.