Remove duplicate values within a cell excel
There are three ways to find and delete duplicates within a cell in Excel. Just choose the one that works best for you. Show
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 cellProblem: 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 cellTo 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 workbookTo 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. RemoveDupeWords function syntaxOur newly created function for removing duplicate text in a cell has the following syntax: RemoveDupeWords(text, [delimiter]) Where:
The function is not case-sensitive, meaning that lowercase and uppercase letters are treated as the same characters. How to use the RemoveDupeWords functionOnce 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:
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. VBA macro to delete duplicate text from multiple cells at onceIf 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:
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 macroInsert 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. How to remove duplicate characters in a cellProblem: 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 charactersTo 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 syntaxThe 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 functionEverything 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. VBA macro to remove the same characters from a cellLike 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 macroAssuming 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.
Remove duplicate substrings with Ultimate SuiteAt 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! Available downloadsExamples to remove duplicates in cell (.xlsm file) You may also be interested inHow do I find duplicates in a single cell?There are three ways to find and delete duplicates within a cell in Excel. Just choose the one that works best for you.. Select a range of cells from which you want to remove repeated text.. Press Alt + F8 to open the Macro dialog box.. In the list of macros, select RemoveDupeWords2.. Click Run.. How do you remove duplicate values in Excel but keep one?How to delete duplicates in Excel but keep one?. Select a cell in your dataset and go to Data > Advanced filter to the far right. ... . Choose to “Filter the list, in-place” or “Copy to another location”. ... . Tick the “Unique records only” box to keep the unique values, and then “OK” to remove all duplicates.. How do you remove duplicates from a cell?Select a cell inside the data which you want to remove duplicates from and go to the Data tab and click on the Remove Duplicates command. Excel will then select the entire set of data and open up the Remove Duplicates window.
What formula can I use to remove duplicates in Excel?To begin with, select the range in which you want to ddelete dupes. To select the entire table, press Ctrl + A. Go to the Data tab > Data Tools group, and click the Remove Duplicates button. The Remove Duplicates dialog box will open, you select the columns to check for duplicates, and click OK.
|