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 :)
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.
To eliminate duplicate text in a cell, you can use the following user-defined function (UDF), named RemoveDupeWords:
To add the above code to your Excel, this is what you need to do:
For more information, please see How to insert VBA code in Excel.
Our newly created function for removing duplicate text in a cell has the following syntax:
The function is not case-sensitive, meaning that lowercase and uppercase letters are treated as the same characters.
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:
If your source data is separated by a space, the second argument should be " " or omitted:
Like any other Excel function, our UDF recalculates automatically when the source data changes, so your results will always be up to date.
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:
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.
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.
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.
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.
To remove duplicated characters within a cell keeping only the first occurrences, you can use the following user-defined function, named RemoveDupeChars:
To insert the function's code into your workbook, the steps are exactly the same as in the previous example.
The syntax of this custom function is as simple as it can possibly be - only one argument is required:
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.
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:
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.
Like RemoveDupeWords, the RemoveDupeChars function can also be called from within a macro:
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.
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.
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:
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!
Table of contents