How to remove duplicates in Excel cell

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.
Remove duplicates in Excel cell

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.
A custom function to remove duplicates within a cell.

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:
Deleting duplicate words separated by a comma and a space

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

=RemoveDupeWords(A2, ",")
Removing repeated words separated by commas

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

=RemoveDupeWords(A2)
Removing duplicates within a cell separated by spaces

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.
    A macro to delete duplicate text from all cells in the selected range

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.
Removing duplicate characters in a cell

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 &amp; 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:
A custom function to delete duplicate characters in a cell

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.
    A macro to remove repeated characters from each cell in the selected range

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).
Remove Duplicate Substrings tool for Excel

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.
Removing duplicated text in Excel without VBA

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 (.exe file)

33 comments

  1. Brilliant! This code helped me a lot, the delimiter can be easily modified so it applies in many cases.

  2. hi
    Thank you so much !!!!

  3. Thanks so much Svetlana! that is very helpful.

  4. Hi if in a one cell name is repeated so many times and there is no delimiter to separate them thant how to delete that duplicates?

  5. Hi there, thank you for your helpful article.

    I am working through how to remove repeated words in Excel cell using =RemoveDupeWords(J5, "_"), I have added the suggested code to a new Module in Visual Basics.

    An example of the data that this formula is being applied to is Bolzano_Bolzano_Project _Stone_Traditional _Format

    Therefore I am applying =RemoveDupeWords(J5, "_") to the data.

    I am not receiving an error message but this formula keeps generating a numerical value, 0. The cell is formatted as 'General'

    My excel knowledge is beginner level but hoping you could please explain what I am doing wrong?

    Thank you,
    Emma

    1. Hi! I used your data and your formula and got the correct value. Try to use the sample file linked at the end of this article.

  6. This can actually be achieved with the use of the Unique function (in Excel 365/Online).

    I have only just discovered this for myself and I am sure there is much more power in the formula than this, but it is working for me, in combination with some other formulas, to remove duplicates from a comma separated list in a cell:

    =TEXTJOIN(",",TRUE,SORT(TRIM(TEXTSPLIT(,,","))))
    Credit here has to go to Exceljet. This formula splits by the delimiter (in this case ","), removes rogue spaces, sorts alphabetically, then stitches back together.

    In my use case I wanted to concatenate several different values, all of which are separated by commas, sort ascending, and remove duplicates. By adding UNIQUE to the above, I actually managed to achieve all the above in one formula:
    =TEXTJOIN(",",TRUE,SORT(UNIQUE(TRIM(TEXTSPLIT(B5,,",")))))

    This tool seems like it could be extremely powerful for string manipulation. Glad I found it and felt like it was worth sharing!!

    1. This was great, I tried the User-defined function and got it all set up but keep getting a #name error so then I read further and found this formula which did the trick. I still like learning the other bits but I don't have all the time in the day to figure this one out.

    2. This formula is unbelievable, I have an if index match text result in a cell that has a lot of duplicated words within it, this formula helped me to avoid using if for about 92 cells long. Thank you, you are a real excel genius

    3. THANK YOU!!!! That formula is AMAZEBALLS!!!

      1. Hey quick follow up question, do you know if there is a way to add to formula if there is nothing in the cell, then put nothing in the cell with the formula? I hope that makes sense - because now when i do it #value comes... and i can work around and get rid of it, but didn't know if you knew of way to add that? Thanks again for posting this!

        1. Use the IFERROR function around the above! I had same situation where I got the #VALUE error if I didn't have any data in the cell yet.

          =IFERROR(TEXTJOIN(", ",TRUE,SORT(UNIQUE(TRIM(TEXTSPLIT(B5,,","))))), "--")

          The "--" at the end is what i wanted to be inputted if there was an error, which you can leave blank " " if you just want the cell to be empty or put "N/A" or whatever you want

        2. I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.

  7. Hi,
    for function "RemoveDupeChars" code line "result = result & char" the message error is Compile error: Expected: End of Statement, do you know how to solve it? thank you

    1. Hi!
      The function works correctly for me. Copy all the function code from the article or use the example file linked to at the end of the article.

      1. For anyone else running into this issue (where "result = result & char" is returning a compile error), try this:

        Old:
        "result = result & char"

        New:
        "result = result & char"

        I hope that helps someone else. Giovanni wrote it correctly in their question, but may have had the original "result = result& char" in their macro (as written in the article proper). This article was still immensely helpful, so thank you!

        1. I think I see the problem now. The characters that are causing the issue are not displaying in these responses. I'll attempt to clarify. The characters I show in brackets (below) are causing the issue:

          Old:
          "result = result &[amp][;] char"

          New:
          "result = result & amp"

          In the old version, there is an ampersand followed by the letters "amp" and then a semicolon, with no spaces between the three items (ampersand, "amp", semicolon). The new version removes those. Giovanni, my bad. You definitely wrote your question correctly, it just didn't display correctly.

  8. Hi. I tried using your code and it doesn't work completely for me. Could you please tell me what to change?

    For example, in one of my cells it says:

    Team project management, Project status meeting, Review & respond to email
    Team project management, Project status meeting, Review & respond to email

    When I use the removedupewords function, it returns the first sentence and the first and last words of the second sentence:

    Team project management, Project status meeting, Review & respond to email
    Team email

    Do you know why the first and last words are being shown when they are supposed to be removed since they are duplicate words?

    1. Hello!
      In your cell, duplicate text strings are separated by line breaks. So use the formula

      =RemoveDupeWords(A2, CHAR(10))

      Or replace the line break with a space using the SUBSTITUTE function.

      =RemoveDupeWords(SUBSTITUTE(A2,CHAR(10)," "), " ")

      1. Hi, I tried replacing the formula in the MACRO but it still isnt working properly for me.

        Do you have any other solution for this issue?

        1. It says that the sub or function is not defined and highlights the CHAR fucntion

  9. I am getting compile error when running it as a function. The error says
    Compile Error:
    Expected : List Seperator or
    And in the code window the first line of the code appears Red as Pasted below.
    RemoveDupeWords(text As String, Optional delimiter As String = " ") As String

  10. After pasting the code for removedupewords in the code window I receive the following error message: Compile error: Expected: Then or GoTo and it highlights the semicolon after "If part &lt".

    I also receive the same error message for semicolon after following line: "If dictionary.Count &gt"

    Thank you!

      1. there's no > and < in the code. What should i replace?

        1. Hi!
          So that html characters do not break your code, download the sample file from the link at the end of the article and copy the VBA code from there.

  11. AMAZING!!! Works perfectly...

    Thank you for the wonderful help

  12. You are great!!!

  13. Real nice user defined function ! Thanks for posting !

    If you are looking for something fun to build... check out a few great functions that Lotus Notes has/had that I wish excel had. @unique, @subset, @explode, @implode. You can easily manipulate a single string and build, dedup and sort string arrays.

  14. Getting a NAME error...

    1. Hi!
      NAME ERROR appears when the function name is written incorrectly. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail.

  15. Hi, doesn't this work in the MAC version? For some reason this returns me #VALUE! error.

    1. Hi Jussi,

      Sorry, our codes are written for Windows, and they won't work in Excel for Mac. In particular, the code cannot be executed because of the following line, as there is no such object in the Mac version:
      Set dictionary = CreateObject("Scripting.Dictionary")

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)