How to highlight duplicate text strings or words in Excel cell

Svetlana Cheusheva by , updated on

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 StringDelimiter = 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 StringDelimiter = 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

Table of contents