Removing duplicates is a common task for most data workers.
While in most cases, you would be working on removing duplicates from a range of cells, sometimes, you may need to remove duplicates from a cell.
For example, below, I have a dataset with duplicate values in each cell and I want to remove the duplicates and keep only the unique ones.
This can quickly be done with a simple formula if you have the new version of Excel that includes functions such as UNIQUE and TEXTSPLIT. If you don’t have these functions in your Excel version, you can use the VBA method I cover in this article.
Let’s get to these methods now.
This Tutorial Covers:
Formula to Remove Duplicates Within a Cell
The new functions Excel has released are amazing and do a lot of heavy lifting.
If you’re using Excel with Microsoft 365, you can use these new functions to remove duplicates from a cell.
Below, I have a dataset where I want to remove the duplicate values (regional name, item name, or person name) from A2:A4.
Note that all these items are separated by a comma followed by a space character, and this is something we can use to split all these items and then get rid of the duplicates.
Below is the formula that will work in our case:
=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,,", "))) 
The above formula uses TEXTSPLIT(A2,”, “) to split the content of the cell into separate rows (in a column) using “, ” as the delimiter.
The result of the TEXTSPLIT function is then used within the UNIQUE function to give us only the unique values from the list.
The result of the UNIQUE function is then used within the TEXTJOIN function that combines the result using the specified delimiter (which is “, ” in our example).
In the final result, the duplicates have been removed.
For this formula to work, it is important to ensure that the delimiter is consistent. For example, in our case, all the items in the cells were separated by a comma followed by a space.
If the delimiter is inconsistent, with spaces present in some cases and not present in others, you can use the below formula. It uses the TRIM function to remove leading and trailing spaces.
=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(TEXTSPLIT(A2,,","))))
The formula covered in this section is not case-sensitive, so it would consider ‘US‘ and ‘us‘ as the same and treat them as duplicates.
Also read: Find and Remove Duplicates in ExcelRemove Duplicates with Multiple Delimeters
If you have items in a cell separated by more than one type of delimiter, you can still use the above formula with a small tweak.
Below, I have a dataset, and I want to remove duplicate values from within the cell:
As you will notice, this example has four different types of delimiters: comma, pipe symbol, dash, and semi-colon.
In this situation, you can use the below formula:
=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(TEXTSPLIT(A2,,{",","-","|",";"}))))
The above formula uses all these delimiters within curly brackets inside the TEXTSPLIT function. This way, the formula assesses each cell for all four delimiters, and each of these is used to split the content of the cells.
VBA Custom Function to Remove Duplicates Within a Cell
If you do not have access to these new functions, you can create your own custom function using VBA. It’s called a User Defined Function (UDF).
Below is the VBA code to create the function:
Function DeDupCells(cellRef As Range, delimiter As String) As String Dim cellValue As String Dim valueArray As Variant Dim uniqueValues As Collection Dim result As String Dim i As Integer ' Get the cell value cellValue = cellRef.Value ' Split the cell value into an array valueArray = Split(cellValue, delimiter) ' Initialize the collection for unique values Set uniqueValues = New Collection ' Loop through the array and add unique values to the collection On Error Resume Next For i = LBound(valueArray) To UBound(valueArray) uniqueValues.Add Trim(valueArray(i)), CStr(Trim(valueArray(i))) Next i On Error GoTo 0 ' Construct the result string from the unique values collection For i = 1 To uniqueValues.Count result = result & uniqueValues(i) & delimiter Next i ' Remove the trailing delimiter If Len(result) > 0 Then result = Left(result, Len(result) - Len(delimiter)) End If ' Output the result DeDupCells = resultEnd FunctionTo use this VBA function, you will have to put this VBA code in a module in the VB Editor in your Excel file.
Where to Put the VBA Code?
Here are the steps to do this so you can use this VBA custom function in the worksheet in Excel:
- Press Alt + F11 to open the Visual Basic for Applications editor. You can also click on the Developer tab and then click on the Visual Basic icon to open the VB editor.
- In the VB editor, go to the menu, click on Insert, and then click on Module. This will create a new module for the Excel file where you can write the code for a custom VBA function.
- In the new module window, copy and paste the above VBA code.
- Close the VB Editor.
Using the Custom Function in Worksheet
When you’re done with the above steps, you can now use the custom function we have created as any other regular function within the cells in your worksheet.
I can use the below formula to remove duplicates from the cell:
=DeDupCells(A2,", ")
Note: Since your Excel file now contains a VBA code, you must save it as a macro-enabled file with a .xlsm extension. This will preserve the code in your file, and you can use this function in the future.
In this article, I showed you two methods you can use to remove duplicates within a cell in Excel using formulas. If you’re using a newer version of Excel that has new functions, such as UNIQUE and TEXTSPLIT, you can use the first method that uses a formula with these functions.
If you do not have access to these functions, you can use my second method, in which I showed you how to create your own custom function using VBA.
I hope you found this article helpful.
If you have any feedback or suggestions for me, please let me know in the comments section.
Other Excel articles you may also like:
- Prevent Duplicate Entries in Excel
- Remove Duplicate Values in Excel Using VBA
- How to Combine Duplicate Rows and Sum the Values in Excel
- How to Filter Cells that have Duplicate Text Strings (Words) in it
