How to highlight duplicate text strings or words in Excel cell

The tutorial shows how to highlight duplicate words or text strings within a cell using VBA.

Excel Conditional Formatting makes it possible to highlight duplicates in every possible way you can think of: with or without 1st occurrences, in a single column or multiple columns, consecutive duplicate cells, and entire rows based on identical values in a key column. But, as usual, there is a "but". Conditional formatting rules work on a cell level while you may want to highlight duplicate text rather than entire cells. This can only be done with macros. Even if you don't have any experience with VBA, please don't rush to close this page. Here, you will find ready-to-use code examples and the detailed instructions on how to use them in your worksheets.

Highlight duplicate words in a cell ignoring text case

This example shows how to shade duplicate words or text strings within a cell in red font color like shown in the image below. Please notice that lowercase and uppercase letters are treated as the same characters. For example, orange, ORANGE and Orange are deemed to be the same word.
Highlight duplicate words in a cell ignoring text case

The macro's code is as follows:

Public Sub HighlightDupesCaseInsensitive() Dim Cell As Range Dim Delimiter As String Delimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", ", ") For Each Cell In Application.Selection Call HighlightDupeWordsInCell(Cell, Delimiter, False) Next End Sub Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True) Dim text As String Dim words() As String Dim word As String Dim wordIndex, matchCount, positionInText As Integer If CaseSensitive Then words = Split(Cell.Value, Delimiter) Else words = Split(LCase(Cell.Value), Delimiter) End If For wordIndex = LBound(words) To UBound(words) - 1 word = words(wordIndex) matchCount = 0 For nextWordIndex = wordIndex + 1 To UBound(words) If word = words(nextWordIndex) Then matchCount = matchCount + 1 End If Next nextWordIndex If matchCount > 0 Then text = "" For Index = LBound(words) To UBound(words) text = text & words(Index) If (words(Index) = word) Then Cell.Characters(Len(text) - Len(word) + 1, Len(word)).Font.Color = vbRed End If text = text & Delimiter Next End If Next wordIndex End Sub

Highlight duplicate text in a cell case-sensitive

In most situations, we tend to ignore the letter case when working with text entries in Excel. Under certain circumstances, however, the text case does matter. For instance, if you are dealing with IDs, passwords, or other records of that kind, the strings such as 1-AA, 1-aa and 1-Aa are not duplicates and should not be highlighted:
Case-sensitive macro to highlight duplicate text in a cell

In this case, use the following version of the code:

Public Sub HighlightDupesCaseSensitive() Dim Cell As Range Dim Delimiter As String Delimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", ", ") For Each Cell In Application.Selection Call HighlightDupeWordsInCell(Cell, Delimiter, True) Next End Sub Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True) Dim text As String Dim words() As String Dim word As String Dim wordIndex, matchCount, positionInText As Integer If CaseSensitive Then words = Split(Cell.Value, Delimiter) Else words = Split(LCase(Cell.Value), Delimiter) End If For wordIndex = LBound(words) To UBound(words) - 1 word = words(wordIndex) matchCount = 0 For nextWordIndex = wordIndex + 1 To UBound(words) If word = words(nextWordIndex) Then matchCount = matchCount + 1 End If Next nextWordIndex If matchCount > 0 Then text = "" For Index = LBound(words) To UBound(words) text = text & words(Index) If (words(Index) = word) Then Cell.Characters(Len(text) - Len(word) + 1, Len(word)).Font.Color = vbRed End If text = text & Delimiter Next End If Next wordIndex End Sub

How to use the macros to highlight duplicates words in Excel

If you are a beginner in using VBA, the below step-by-step instructions will comfortably walk you through. Experienced users may just pick the download link and skip the rest :)

Add the code to your workbook

You start with inserting the macro's code in your Excel workbook. Here's how:

  1. Open the workbook where you want to highlight dupes.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the code in the Code window.
  5. To keep the macro for future use, be sure to save your workbook as a macro-enabled .xlsm file.

Alternatively, you can download our sample workbook and run the macro from there. The sample workbook contains the following macros:

  • HighlightDupesCaseInsensitive - shades duplicates within a cell ignoring the letter case.
  • HighlightDupesCaseSensitive - highlights dupes in a cell considering the letter case.

For more information, please see How to insert VBA code in Excel.

Run the macro

With the code added to you own workbook or our sample file downloaded and open, run the macro in this way:

  1. In your worksheet, select the cells where you wish to highlight duplicate text. This can be one range or multiple non-adjacent ranges.
  2. Press Alt + F8.
  3. Select the macro of interest and click Run.
    Run the macro to highlight duplicates words in Excel.
  4. The macro will ask you to specify the delimiter that separates the values in the selected cells. The preset delimiter (a comma and a space in our case) will appear in the input box automatically. Depending on your needs, you can leave the default delimiter or type a different one, and then click OK.
    Enter the delimiter that separates the values in the selected cells.

A moment later, all duplicate strings in the selected cells will be shaded in red color (or whatever font color is set in your code).

Tip. To quickly remove duplicates within a cell, you can utilize Remove Duplicate Substrings, one of many timesaving tools included in our Ultimate Suite.

How to adjust the code for your needs

With these usage notes and the very basic knowledge of VBA (or just closely following the below instructions), you can easily modify the codes in exact accordance with your needs.

Place on the same module

As you may notice, both macros (HighlightDupesCaseSensitive and HighlightDupesCaseInsensitive) call the HighlightDupeWordsInCell function. The difference between the two macros above is only in the 3rd parameter (CaseSensitive) passed to the said function.

For case-sensitive search, it is set to TRUE:

Call HighlightDupeWordsInCell(Cell, Delimiter, True)

For case-insensitive search, it is set to FALSE:

Call HighlightDupeWordsInCell(Cell, Delimiter, False)

For the macros to work, the code of the HighlightDupeWordsInCell function must be placed onto the same module as the macros.

Delimiter

When run, the macro will ask you to specify the delimiter that separates words/strings in the selected cells. The default delimiter is a comma and a space (", ") and it is preset in the InputBox:

Delimiter = InputBox("Specify the delimiter that separates values in a cell", "Delimiter", ", ")

In your code, you are free to use any other character(s) as the predefined delimiter.

Color

By default, the HighlightDupeWordsInCell function shades duplicates in red font color. The color is defined in this line:

Cell.Characters(positionInText, Len(word)).Font.Color = vbRed

Here, vbRed is a sort of VBA color constant. To display dupes in a different color, you can replace vbRed with another constant such as vbGreen, vbYellow, vbBlue, etc. The list of supported color constants can be found here.

That's how to highlight duplicate words in Excel cells. I thank you for reading and hope to see you on our blog next week!

Available downloads

Code examples to highlight duplicates in a cell (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)

You may also be interested in

8 comments

  1. Hi, is there a way to highlight duplicate text in a string that appears in more than one cell, rather than just in the same cell? For example:

    apple, pear, orange
    emma, ava
    apple
    ava, jane

    Apple and ava are both repeated in more than one cell and I'd like those words to be highlighted.

  2. Hello Alexander,
    How would the code be modified if I need to remove the delimiter? My use case is the following:
    I have a single column of numbers stored as text, 20-digit long, I need to search for duplicates. Your code works only if I add a comma or a dot somewhere, as it acts as a delimiter. But I have no decimal values.
    Could you please send me a modified script that does not include the delimiter requirement?
    Thank you

    1. Hello!
      Without separators, it is impossible to extract individual values from the text.
      If a cell contains multiple values, they must be separated by delimiters. If I understand correctly, in your case each digit is a separate value. Extract each digit from the cell using the MID function. Combine and insert separators between digits using the TEXTJOIN function.
      For example,

      =TEXTJOIN("/",TRUE,MID(A1,ROW($A$1:$A$100),1))

      Now you can use the macro.

  3. I have a big sheet and it have a coloumn which is the many stations name, which shows that the cable cut of that stations, I am preparing a report which tells which cuts are how many times come and which station it is repeated again and again, so it is find by duplicate manually that stations,
    Which data i share it is actually the repeated cut stations which I find manually, I want a formula to trace out that repeated cut
    Kindly help me, bundle of thanks in advance,
    If you want i can share my data please tell me the link then i will post it,

  4. I'd like to highlight duplicate words within the whole sheet or workbook rather than within each cell. Is this possible

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)