如何在Excel列中忽略大小写与标点高亮重复单词

以下是可直接使用的VBA宏代码，能帮你在指定单列中忽略大小写、去除指定标点后，检测并高亮重复出现的单词：

Sub HighlightDuplicateWords()
    Dim targetRange As Range
    Dim cell As Range
    Dim wordDict As Object
    Dim cleanedWords As Variant
    Dim i As Integer
    Dim cleanedWord As String
    Dim punctuation As String
    
    ' 定义需要去除的标点符号，可按需添加
    punctuation = "./,;:'""!?()-[]{}"
    
    ' 检查是否选中有效区域
    If TypeName(Selection) <> "Range" Then
        MsgBox "请先选中要处理的单列区域！", vbExclamation
        Exit Sub
    End If
    Set targetRange = Selection
    Set wordDict = CreateObject("Scripting.Dictionary")
    wordDict.CompareMode = vbTextCompare ' 开启字典忽略大小写模式
    
    ' 第一步：遍历单元格，统计清理后单词的出现次数
    For Each cell In targetRange
        If cell.Value <> "" Then
            ' 去除单元格内的标点
            cleanedWord = cell.Value
            For i = 1 To Len(punctuation)
                cleanedWord = Replace(cleanedWord, Mid(punctuation, i, 1), "")
            Next i
            ' 按空格拆分单词
            cleanedWords = Split(Trim(cleanedWord), " ")
            ' 统计每个单词的出现次数
            For Each word In cleanedWords
                If word <> "" Then
                    If wordDict.Exists(word) Then
                        wordDict(word) = wordDict(word) + 1
                    Else
                        wordDict(word) = 1
                    End If
                End If
            Next word
        End If
    Next cell
    
    ' 第二步：再次遍历单元格，高亮重复单词
    For Each cell In targetRange
        If cell.Value <> "" Then
            cleanedWord = cell.Value
            ' 再次去除标点
            For i = 1 To Len(punctuation)
                cleanedWord = Replace(cleanedWord, Mid(punctuation, i, 1), "")
            Next i
            cleanedWords = Split(Trim(cleanedWord), " ")
            
            ' 匹配原单元格中的单词并高亮
            For Each word In cleanedWords
                If word <> "" And wordDict(word) > 1 Then
                    Dim originalWord As String
                    originalWord = GetOriginalWord(cell.Value, word, punctuation)
                    If originalWord <> "" Then
                        Dim startPos As Integer
                        startPos = 1
                        Do
                            startPos = InStr(startPos, cell.Value, originalWord, vbTextCompare)
                            If startPos > 0 Then
                                cell.Characters(startPos, Len(originalWord)).Font.Color = vbRed ' 高亮为红色，可修改
                                startPos = startPos + Len(originalWord)
                            End If
                        Loop While startPos > 0
                    End If
                End If
            Next word
        End If
    Next cell
    
    MsgBox "重复单词高亮完成！", vbInformation
End Sub

' 辅助函数：从原文本中提取带标点的原始单词
Function GetOriginalWord(originalText As String, cleanedWord As String, punctuation As String) As String
    Dim tempWords As Variant
    Dim tempWord As String
    Dim cleanedTempWord As String
    Dim i As Integer
    
    tempWords = Split(Trim(originalText), " ")
    
    For Each tempWord In tempWords
        cleanedTempWord = tempWord
        ' 清理临时单词的标点
        For i = 1 To Len(punctuation)
            cleanedTempWord = Replace(cleanedTempWord, Mid(punctuation, i, 1), "")
        Next i
        ' 忽略大小写匹配
        If StrComp(cleanedTempWord, cleanedWord, vbTextCompare) = 0 Then
            GetOriginalWord = tempWord
            Exit Function
        End If
    Next tempWord
    
    GetOriginalWord = ""
End Function

使用步骤： #

1. 打开Excel文件，按下Alt + F11打开VBA编辑器；
2. 右键点击左侧项目窗口中的当前工作簿，选择「插入」→「模块」；
3. 将上述代码粘贴到新建模块中；
4. 返回Excel界面，选中需要处理的单列区域；
5. 按下Alt + F8，选择HighlightDuplicateWords宏，点击「执行」。

自定义选项： #

• 若需添加更多忽略的标点，修改punctuation = "./,;:'""!?()-[]{}"一行，在引号内补充对应符号；
• 高亮颜色可替换：将vbRed改为vbBlue（蓝色）、vbYellow（黄色），或使用RGB值如RGB(255, 192, 0)（橙色）。

内容的提问来源于stack exchange，提问作者user329683