How do i remove comma separated duplicate values in excel?

There are three ways to find and delete duplicates within a cell in Excel. Just choose the one that works best for you.

When it concerns removing duplicate values or rows, Microsoft Excel offers an array of different options. But when it comes to removing identical text within a given cell, Excel provides… nothing. No tools, no features, no formulas, no nothing. Will it stop us from achieving our goal? In no case. If Excel does not have the function we need, let's go write our own one :)

How to remove repeated words in Excel cell

Problem: You have the same words or text strings in a cell and would like to remove the second and all subsequent repeats.

Solution: a custom user-defined function or VBA macro.

User-defined function to remove duplicates within a cell

To eliminate duplicate text in a cell, you can use the following user-defined function (UDF), named RemoveDupeWords:

Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String Dim dictionary As Object Dim x, part Set dictionary = CreateObject("Scripting.Dictionary") dictionary.CompareMode = vbTextCompare For Each x In Split(text, delimiter) part = Trim(x) If part <> "" And Not dictionary.Exists(part) Then dictionary.Add part, Nothing End If Next If dictionary.Count > 0 Then RemoveDupeWords = Join(dictionary.keys, delimiter) Else RemoveDupeWords = "" End If Set dictionary = Nothing End Function

How to insert the function's code in your workbook

To add the above code to your Excel, this is what you need to do:

1. Press Alt + F11 to open the Visual Basic Editor.
2. On the left pane, right-click ThisWorkbook and select Insert > Module.
3. Paste the above code in the Code window.

For more information, please see How to insert VBA code in Excel.

RemoveDupeWords function syntax

Our newly created function for removing duplicate text in a cell has the following syntax:

RemoveDupeWords(text, [delimiter])

Where:

• Text (required) - a string or cell from which you want to delete repeated text.
• Delimiter (optional) - the delimiter that the repeated text is separated by. If omitted, a space is used for the delimiter.

The function is not case-sensitive, meaning that lowercase and uppercase letters are treated as the same characters.

How to use the RemoveDupeWords function

Once the function's code is added to your workbook, you can use it in your formulas the same way as you use Excel's built-in functions.

Just start typing the function's name after the equal sign, and it will appear in the formula intellisense. Double-click on the function, and you'll have it inserted in a cell. Define the arguments, type the closing parenthesis, press Enter, and your formula is completed.

For example, to delete duplicate words separated by a comma and a space from A2, enter the below formula in B2, and then drag it down through as many cells as needed:

=RemoveDupeWords(A2, ", ")

As the result, you will have a list of unique words or substrings separated by a comma and space:

If you'd rather get a comma-separated list, then use only a comma for the delimiter:

=RemoveDupeWords(A2, ",")

If your source data is separated by a space, the second argument should be " " or omitted:

=RemoveDupeWords(A2)

Like any other Excel function, our UDF recalculates automatically when the source data changes, so your results will always be up to date.

VBA macro to delete duplicate text from multiple cells at once

If you are looking to remove repeated text from multiple cells in one go, then you can call the RemoveDupeWords function from within a macro. In this case, the delimiter is hardcoded, and you will have to update the macro's code every time the delimiter changes. Alternatively, you can write a few code variations for the most common delimiters, say, a space, comma, or comma and space, and give your macros meaningful names, e.g. RemoveDupesDelimSpace.

The macro's code is as follows:

Public Sub RemoveDupeWords2() Dim cell As Range For Each cell In Application.Selection cell.Value = RemoveDupeWords(cell.Value, ", ") Next End Sub

In the above code, the delimiter is a comma and space. To use a different delimiter, replace ", " with another character(s) in this code line:

cell.Value = RemoveDupeWords(cell.Value, ", ")

Note. For the macro to work, its code and the RemoveDupeWords function's code must be placed onto the same module.

How to use the macro

Insert the macro's code in your own workbook or open our sample workbook with the code, and then perform the following steps to run the macro.

1. Select a range of cells from which you want to remove repeated text.
2. Press Alt + F8 to open the Macro dialog box.
3. In the list of macros, select RemoveDupeWords2.
4. Click Run.
   
   

For more details, please see How to run a macro in Excel.

Note. Because a macro's action cannot be undone, we strongly recommend saving your workbook right before using the macro. This way, if something goes wrong, you can simply close and reopen the workbook, and you'll be back to exactly where you were. Or you can just make a copy of the worksheet(s) that might be affected by the macro.

How to remove duplicate characters in a cell

Problem: You have multiple occurrences of the same character in a cell, while each cell should only contain a single occurrence of a given character.

Solution: a custom user-defined function or VBA macro.

User-defined function to delete repeated characters

To remove duplicated characters within a cell keeping only the first occurrences, you can use the following user-defined function, named RemoveDupeChars:

Function RemoveDupeChars(text As String) As String Dim dictionary As Object Dim char As String Dim result As String Set dictionary = CreateObject("Scripting.Dictionary") For i = 1 To Len(text) char = Mid(text, i, 1) If Not dictionary.Exists(char) Then dictionary.Add char, Nothing result = result & char End If Next RemoveDupeChars = result Set dictionary = Nothing End Function

To insert the function's code into your workbook, the steps are exactly the same as in the previous example.

RemoveDupeChars function syntax

The syntax of this custom function is as simple as it can possibly be - only one argument is required:

RemoveDupeChars(text)

Where text is a string or cell from which you wish to remove duplicate characters.

The function is case-sensitive and treats lowercase and uppercase letters as different characters.

How to use RemoveDupeChars function

Everything we said about the use of RemoveDupeWords is true for RemoveDupeChars. So, without going too much into theory, let's get straight to an example.

To delete duplicate characters from column A beginning in A2, enter this formula in B2 and copy it down:

=RemoveDupeChars(A2)

As you can see in the image below, the function successfully handles different character types including letters, digits and special symbols:

Tip. If your characters are separated from each other by some delimiter such as a space, comma or hyphen, then use the RemoveDupeWords function as shown in the previous example.

VBA macro to remove the same characters from a cell

Like RemoveDupeWords, the RemoveDupeChars function can also be called from within a macro:

Public Sub RemoveDupeChars2() Dim cell As Range For Each cell In Application.Selection cell.Value = RemoveDupeChars(cell.Value) Next End Sub

Because this UDF does not use any delimiter, you won't have to make any adjustments in the code.

Note. For the macro to work, its code and the code of RemoveDupeChars UDF must be placed onto the same module in the VBA editor.

How to use the macro

Assuming you've already inserted the macro's code in your workbook or opened our sample workbook containing the code, launch the macro in this way.

1. Select a range of cells from which you wish to remove repeated characters.
2. Press Alt + F8 to open the Macro dialog box.
3. In the list of macros, select RemoveDupeChars2.
4. Click Run.
   

Remove duplicate substrings with Ultimate Suite

At the beginning of this tutorial, it was mentioned that Microsoft Excel does not have an inbuilt feature for removing duplicates within a cell. But our Ultimate Suite does!

You can find it in the Duplicate Remover drop-down menu on the Ablebits Data tab, in the Dedupe group. If the Remove Duplicate Substrings option does not appear in your Excel, make sure you have the latest version of Ultimate Suite installed (a free trial can be downloaded here).

To remove repeated words or text from multiple cells in 5 seconds (a second per step :), this is what you need to do:

1. Select your source data and launch the Remove Duplicate Substrings tool.
2. Specify the delimiter.
3. Define whether to treat consecutive delimiters as one (default).
4. Choose whether to perform case-sensitive or case-insensitive search.
5. Click Remove.

Done! No fiddling with VBA or formulas, just quick and accurate results.

To learn more about this awesome add-in, please visit its home page. Or even better, download an evaluation version below and give it a try!

That's how to remove duplicate text in a cell. I thank you for reading and hope to see you on our blog next week!

Available downloads

Examples to remove duplicates in cell (.xlsm file)
Ultimate Suite 14-day fully-functional version (.zip file)

You may also be interested in

How do I remove duplicates from a comma separated string in Excel?

How do I remove repetitive text in Excel?

How do I remove duplicate records based on values in Excel?

What is the easiest way to remove duplicates in Excel?