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

Table of contents

28 comments

  1. Hi
    I would like to highlight cells whenever they contain a word duplicated from a particular cell in the same roll.
    Example:

    Column A is "list of missing items" and Columns B, C, D, E are tasks for which items in A would be needed for. I would like to highlight cells in columns B-E wherever they contain a word that is also present in the corresponding cell in column A (missing items).

    E.g. if cells A3 contains (Sugar, Tomato, RICE) and then B3 (xxx, bbb, zzzz); C3 (tomato, eggs, salt), D3 (sugar, ttt, Rice); E3 (fff, BBB, jjj) - then highlight cells C3 and D3, BUT not A3. I would to apply formula for all roles

    • Hi! A conditional formatting rule cannot split a cell into separate words and search for those words in other cells. I'm really sorry, looks like this is not possible with the standard Excel options.

  2. Hi, this macro wont run and it gives me a syntax error for this part:

    Dim Delimiter As StringDelimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", " OR ")For Each Cell In Application.Selection

    Can you help?

    • Hi! I could not reproduce your problem. Try to copy the function code again, or use the sample file that is linked to at the end of this article.

    • this issue happened to me too; is there anything to do with the excel version? i'm using the newest version.

      • Hello!

        The issue stemmed from mangled formatting in our code, most likely occurring during the upgrade to a new version of our blog engine. Some carriage returns were inadvertently removed, causing statements intended for new lines to be merged with previous lines, resulting in the error. In this particular case, "For Each Cell In Application.Selection..." should be in a new line. We apologize for any confusion caused. The code has been fixed. To prevent potential formatting issues when copying from the web, you can also download our sample workbook containing the accurate code.

  3. 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.

  4. Hello!

    I have a schedule done in Excel, where most values in a range are names of employees and have the same rules (i.e. they should appear twice, and anything less or more appears in a different color). However, I have three employees that should only appear once rather than twice, and anything below or over one needs to show up in a unique color. I know how to put multiple rules/formulas for rows or cells based on values or duplicates, but I cannot figure how to write new rules only when specific text (names of employees) pops up. The names can appear in multiple rows and columns, so I cannot isolate certain cells.

    Thank you!

  5. 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

    • 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.

  6. Is there a way to ingore certain words from being marked?

  7. This information is great and works for one column, however, may you please assist me if I want to highlight duplicates in multiple columns.
    Example would be as follows:
    Column1 Column 2
    74664 12345, 74664, 74664
    55352 55352, 74664, 66535
    88577 55344,88577, 72774,88577
    20055 20055
    87677 65786, 39938, 87677
    84885 88485

      • Thanks but I am aware of the instructions you have provided. What I was asking is, how to highlight duplicate text strings or words in Excel cell when multiple columns have many text strings/words and want to highlight specific duplicates.

        For instance, if you have a macro where I can highlight just the specific duplicates from column 1 and column 2:

        Column1 Column 2
        74664 12345, 74664, 74664
        55352 55352, 74664, 66535
        88577 55344,88577, 72774,88577
        20055 20055
        87677 65786, 39938, 87677
        84885 88485

  8. Thanks a lot Sir,
    Where I upload my data, kindly guide me?

  9. Hello Sir,
    I hope you will be fine, I use the macro code for ignoring case sensitive in a work book have multiple sheets but it is not working,
    Kindly check and help me.

  10. Hello Sir,
    I studied and tried many times, but not find the solution ,
    Kindly help me.

  11. 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,

  12. Hello Sir,
    I hope you will fine, I try this but not successful,
    you see sr.no.1,11 are the duplicate cell phrase i find it manually.
    same like sr.no.6&17,
    this a very big sheets of data,
    kindly any other help.

  13. Kindly help to find out the duplicate entries with formula or vba from a coloum B
    1 MSTN: 065 Chowk Hanjerwal to Wapda Office Azam Garden & Azam Garden Gate
    6 GLB: OLT-3 to Khwaja Behari Darbar Main Meer Colony
    11 MTN: Wapda Office Azam Garden to 065 Hanjarwal Chowk to C-40 Opp Main Gate
    17 GLB: GLB OLT-3 to Khawaja Bihari Darbar Mian Mir Colony
    21 BTN: BTN OLT-1 to DC-2 Jasmine Block
    27 BTN: 1375-Mal Exchange HW PTN970 to MSAG-1 Bahria Orchard near Bahria Office (Outdoor)
    31 BTN: OLT-1 to DC-2 Jasmine Block
    33 BTN: 1375-MAL Exchange to MSAG-1 Bahria Orchard near Bahria Office (Outdoor)
    36 TNB: Tops Block Park View to Hi Noon Lab
    38 TNB: Hi Noon Lab to Tops Block Park View
    43 GLRV: IP Node to B-4 Chaburji Park to C-2 P&T Colony
    47 SMD: OLT to B-36 Opp. Shadman Market
    55 MLRD: OLT to C-35 near Ilyas Autos to C-40 H-Block Opp. Sabzazar Grid Station

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)