How to remove duplicates within a cell in Excel

Detailed explanation of how to delete duplicates in Excel cells

This article introduces three ways to find and delete duplicates in Excel cells, and just choose the method that suits you best.

Microsoft Excel offers a variety of different options when it comes to deleting duplicate values ​​or rows. However, when it is necessary to delete the same text within a given cell, Excel... provides nothing. No tools, no functions, no formulas, nothing. Will this prevent us from achieving our goals? Of course not. If Excel doesn't have the features we need, let's write one ourselves!

• Use VBA to remove duplicate words in Excel
• Use VBA to delete duplicate characters
• Use Ultimate Suite to delete duplicate text in multiple cells

How to delete duplicate words in Excel cell

Problem: There is the same word or text string in the cell and you want to delete the second and all subsequent duplicates.

Solution: Customize user-defined functions or VBA macros.

User-defined functions that delete duplicates in cells

To eliminate duplicate text in a cell, you can use the following custom user-defined function (UDF) called 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 function code in a workbook

To add the above code to your Excel you need to do the following:

1. Press Alt F11 to open the Visual Basic Editor.
2. In the left pane, right-click ThisWorkbook and select Insert > Modules .
3. Paste the above code into the code window.

For more information, see How to Insert VBA Code in Excel.

RemoveDupeWords function syntax

Our newly created function to delete duplicate text in a cell has the following syntax:

RemoveDupeWords(text, [delimiter]) where:

• Text (Required) - A string or cell from which to delete duplicate text.
• Delimiter (optional) - A delimiter for duplicate text to be separated by it. If omitted, use spaces as delimiters.

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

How to use the RemoveDupeWords function

After adding the function code to the workbook, you can use it in your formula like using Excel's built-in functions.

Just start typing the function name after the equal sign and it will appear in formula intelligence. Double-click the function and it will be inserted into the cell. Define the parameters, type the closing bracket, press Enter, and your formula is finished.

For example, to remove duplicate words separated by commas and spaces from A2, enter the following formula in B2 and drag it down to the desired number of cells:

=RemoveDupeWords(A2, ", ")

As a result, you get a list of unique words or substrings separated by commas and spaces :

If you want to get a comma-separated list, use commas only for the delimiter:

=RemoveDupeWords(A2, ",")

If your source data is separated by spaces , the second parameter should be " " or omitted:

=RemoveDupeWords(A2)

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

VBA macro that deletes duplicate text in multiple cells at once

If you want to delete duplicate text in multiple cells at once, you can call the RemoveDupeWords function in the macro. In this case, the delimiter is hardcoded, and you have to update the macro's code every time the delimiter changes. Alternatively, you could write some code variation for the most common delimiters such as spaces, commas or commas and spaces and give your macros a meaningful name, such as RemoveDupesDelimSpace .

The macro 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 separator is a comma and a space . To use a different delimiter, replace ", " with other characters in the following line of code:

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

Notice. For the macro to work properly, its code and the code of the RemoveDupeWords function must be placed in the same module.

How to use macros

Insert the macro code into your own workbook or open a sample workbook containing the code and perform the following steps to run the macro.

1. Select the range of cells from which you want to remove duplicate 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, see How to run macros in Excel.

Notice. Since macro operations cannot be undoped , we strongly recommend that you save your workbook before using the macro. This way, if something goes wrong, you can simply close and reopen the workbook and you will return to where you were before. Alternatively, you can copy only sheets that may be affected by macros.

How to remove duplicate characters in a cell

Problem: There are multiple identical characters in a cell, and each cell should contain only a single occurrence of the given character.

Solution: Customize user-defined functions or VBA macros.

User-defined functions that remove duplicate characters

To remove duplicate characters in a cell, keeping only the first occurrence of characters, you can use the following custom user-defined function called 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 function code into your workbook, the steps are exactly the same as the previous example.

RemoveDupeChars function syntax

The syntax of this custom function is as simple as possible - only one parameter is required:

RemoveDupeChars(text) where text is a string or cell from which duplicate characters are to be removed.

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

How to use the RemoveDupeChars function

Everything that we said about RemoveDupeWords works with RemoveDupeChars . So, without too much theory, let's go straight to the example.

To remove duplicate characters from column A (starting from A2), enter this formula in B2 and copy it below:

=RemoveDupeChars(A2)

As shown in the figure below, the function successfully handles different character types, including letters, numbers and special symbols:

hint. If your characters are separated from each other by certain delimiters such as spaces, commas, or hyphen, then use the RemoveDupeWords function shown in the previous example.

Delete VBA macros with the same characters in a cell

Like RemoveDupeWords , the RemoveDupeChars function can also be called in macros:

 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 separators, you do not need to make any adjustments in your code.

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

How to use macros

Assuming you have inserted the macro code into your workbook or opened a sample workbook containing the code, start the macro as follows.

1. Select the range of cells from which you want to remove duplicate characters.
2. Press Alt F8 to open the macro dialog box.
3. In the list of macros, select RemoveDupeChars2 .
4. Click Run .

Use Ultimate Suite to delete duplicate substrings

At the beginning of this tutorial, it is mentioned that Microsoft Excel does not have built-in functionality to delete duplicates within cells. But our Ultimate Suite can!

You can find it in the Duplicate Remover drop-down menu on the Ablebits Data tab, located 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 available here).

To remove duplicate words or text from multiple cells at 5 seconds (each step), you need to do the following:

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

Completed! No VBA or formulas are required, just quick and accurate results.

To learn more about this powerful add-in, visit its homepage. Or better yet, download the evaluation version below and try it out!

This is how to delete duplicate text in a cell. Thank you for reading and hope to see you next week on our blog!

Downloadable resources

Example of deleting duplicates in a cell (.xlsm file) Ultimate Suite 14-day full feature version (.exe file)

The above is the detailed content of How to remove duplicates within a cell in Excel. For more information, please follow other related articles on the PHP Chinese website!