How to highlight duplicate text / words in Excel cell

This tutorial demonstrates how to use VBA to highlight duplicate words or phrases within an Excel cell. While Excel's built-in conditional formatting offers extensive duplicate highlighting options, it operates at the cell level. This macro allows for highlighting specific text within a cell.

Excel's Conditional Formatting excels at highlighting duplicates in various ways—with or without the first occurrence, across single or multiple columns, consecutive duplicates, or entire rows based on a key column. However, it lacks the ability to highlight duplicate text segments within a single cell. This limitation necessitates the use of macros.

This guide provides ready-to-use VBA code and instructions, even for those without prior VBA experience.

Highlighting Duplicate Words (Case-Insensitive)

This macro highlights duplicate words within a cell using a red font, regardless of capitalization. "orange," "ORANGE," and "Orange" are treated as identical.

VBA Code (Case-Insensitive):

Public Sub HighlightDupesCaseInsensitive()
  Dim Cell As Range
  Dim Delimiter As String
  Delimiter = InputBox("Enter the delimiter separating values (e.g., ', ')", "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

Highlighting Duplicate Text (Case-Sensitive)

This version distinguishes between uppercase and lowercase letters. "1-AA," "1-aa," and "1-Aa" are considered distinct.

VBA Code (Case-Sensitive):

(The code is nearly identical to the case-insensitive version; only the CaseSensitive parameter in the HighlightDupeWordsInCell call and the absence of LCase in the words = Split(...) line differ. Therefore, it's omitted for brevity to avoid redundancy.) The key difference lies in setting the CaseSensitive parameter to True when calling HighlightDupeWordsInCell.

Using the Macros

1. Add the Code: Open your Excel workbook, press Alt F11 to open the VBA editor, insert a module, and paste the code. Save as a macro-enabled workbook (.xlsm).
2. Run the Macro: Select the cells, press Alt F8, choose the macro (Case-Sensitive or Case-Insensitive), and click "Run."
3. Specify Delimiter: The macro prompts for a delimiter (e.g., ", "). Enter the character(s) separating words in your cells.

The duplicate text segments will be highlighted in red. To remove duplicates within a cell, consider using a dedicated tool like the "Remove Duplicate Substrings" function from a suite of Excel utilities.

Customizing the Code

• Module Placement: Both macros call the HighlightDupeWordsInCell subroutine. Ensure this subroutine is in the same module.
• Delimiter: Change the default delimiter in the InputBox line as needed.
• Color: Modify vbRed to another VBA color constant (e.g., vbGreen, vbBlue) to change the highlight color.

This detailed guide empowers you to efficiently highlight duplicate text within Excel cells using VBA. Remember to download the provided sample workbook for a practical demonstration.

The above is the detailed content of How to highlight duplicate text / words in Excel cell. For more information, please follow other related articles on the PHP Chinese website!