How to remove duplicate characters or words in string of an Excel cell?

When working with data in Excel, it is quite common to encounter situations where cells contain repeated characters or duplicate words. For example, you might have user-generated input, exported logs, or product codes in which characters or words may appear multiple times. Removing these duplicates can help clean up your data for further analysis, improve readability, or prepare strings for use in other systems.

This guide introduces several methods for removing duplicate characters or duplicate words within a single cell. You can choose the most suitable solution based on your Excel version, technical comfort, and the type of delimiter used in your data. Whether your data involves individual characters or entire words separated by spaces or punctuation, there’s a practical approach below you can use.

• Remove duplicate characters or words using Excel Formula (Dynamic Array)
• Remove duplicate characters of text string with User Defined Function
• Remove duplicate words separated by punctuation with User Defined Function

Remove duplicate characters or words using Excel Formula (Dynamic Array)

If you are using Microsoft 365 or Excel 2021 and later, you can take advantage of dynamic array formulas to remove duplicate characters or words from a cell—entirely without VBA. These functions, such as TEXTSPLIT, UNIQUE, TEXTJOIN, and MID, make it easy to split, deduplicate, and reconstruct the string directly in your worksheet.

This method is especially recommended if you prefer not to use macros, want an efficient solution, and are comfortable with Excel’s latest formula features.

Pros: Instantly recalculates when source data changes, no need for macro enablement or special permissions, formula can easily be applied to large data ranges.
Cons: Only works in Excel for Microsoft 365, Excel 2021 or newer (does not work in Excel 2019 or earlier).

To remove duplicate characters within a cell (e.g., "banana" → "ban"):

1. Select the cell where you want to display the result (for example, cell B2 next to your source cell A2) and enter the following formula:

=TEXTJOIN("",,UNIQUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

This formula splits the contents of A2 into single characters using MID, generates sequential positions using ROW(INDIRECT(...)), extracts unique characters with UNIQUE, and then combines them back into a text string with TEXTJOIN.

2. Press Enter. The formula result will display the string in A2 with duplicate characters removed, order preserved. To apply the same operation for other rows, use the fill handle to copy the formula down as needed.

Note: If you see a #NAME? error, your Excel version may not support these functions. Please check that you are using Excel for Microsoft 365 or Excel 2021 or later.

Troubleshooting: Sometimes, extra spaces or punctuation may interfere with results. Consider using TRIM or TEXTSPLIT with additional arguments if your data is not consistently formatted.

Using dynamic array formulas helps keep your workflow efficient and up to date and simplifies data cleaning tasks for both simple and complex string patterns.

Remove duplicate characters of text string with User Defined Function

If you have a range of cells containing text strings and need to remove duplicate characters from each cell—for example, turning "aabbcc" into "abc" as illustrated below—a custom VBA function can help you accomplish this quickly, especially if your Excel version does not support advanced array formulas.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window. Make sure to add the code close to the beginning of the module to avoid conflicts with other macros you may have.

VBA code: Remove duplicate characters of text string in a cell

Function RemoveDupes1(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupes1 = xOutValue
End Function

3. Then after saving and closing the VBA code window, return to your worksheet. Enter this formula =removedupes1(A2) in a blank cell next to your original text (here, A2 is the cell containing the string you want to process). See illustration below:

4. Press Enter to calculate the result. Drag the fill handle down to apply the function to all other relevant cells in the column, and you will see that all duplicate characters are removed from each corresponding cell immediately.

Tip: The order of appearance is preserved—the function keeps the first occurrence of each character and removes subsequent duplicates within the cell.

Precaution: User Defined Functions (UDFs) added via VBA are only available in the workbook where the code is stored. If you move or share your workbook, ensure the macro is included or reinserted in the target file. Also, macros need to be enabled for the code to run.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

Remove duplicate words separated by punctuation with User Defined Function

In situations where your data consists of words separated by punctuation—such as commas, semicolons, or other symbols—you may wish to remove repeated words within each cell while preserving only the first instance of each word. This is especially useful for tags lists, categories, or addresses. You can accomplish this efficiently using a customized VBA function.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window. Place the code at the beginning of a new module if possible, for clarity.

VBA code: Remove duplicate words separated by punctuation of a cell

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
    Dim x
    'Updateby Extendoffice
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
    End With
End Function

3. After saving and closing the VBA window, go back to your worksheet. In a blank column adjacent to your data, enter this formula: =RemoveDupes2(A2,","). Here, A2 refers to your target cell and "," indicates the punctuation mark used as a separator. You can replace this comma with another character (such as ";" or space) if your words are divided by a different punctuation.

4. Press Enter to confirm, then drag down the formula to fill other cells below as needed. After this step, all duplicate words are removed from their respective cells, leaving unique words in original order. See result below:

Note: In the formula, you can change both the cell reference and the punctuation separator as appropriate for your data. If multiple characters or different types of separators (such as space and comma combined) are used, further code adjustment may be required.

Tip: Like with all VBA macros, remember to keep a backup of your workbook and enable macros to use this function.