如何修改Excel VBA脚本实现整词匹配过滤列表内容

The issue with your original script is that Application.WorksheetFunction.Search looks for substrings anywhere in the text—even tucked inside other words. To target full, standalone words only, we’ll use regular expressions (regex) in VBA, which lets us define strict word boundaries to avoid unwanted partial matches.

Modified VBA Script #

Sub GetFullWordsOnly()
    Dim wrdLRow As Long
    Dim wrdLp As Long
    Dim CommentLrow As Long
    Dim CommentLp As Long
    Dim Sht As Worksheet
    Dim regEx As Object
    Dim currentComment As String
    Dim matchedWords As String
    
    ' Initialize regex object for word matching
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.IgnoreCase = True ' Keep case-insensitive matching (matches "how" to "How")
    regEx.Global = False ' We only need to check if the word exists once per comment
    
    ' Define your target worksheet
    Set Sht = Sheets("Sheet1")
    
    ' Get last rows for words (Column A) and comments (Column C)
    wrdLRow = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row
    CommentLrow = Sht.Cells(Sht.Rows.Count, "C").End(xlUp).Row
    
    ' Loop through each comment in Column C
    For CommentLp = 2 To CommentLrow
        currentComment = Sht.Cells(CommentLp, "C").Value
        matchedWords = ""
        
        ' Loop through each target word in Column A
        For wrdLp = 2 To wrdLRow
            Dim targetWord As String
            targetWord = Sht.Cells(wrdLp, "A").Value
            
            ' Regex pattern: match the word only as a standalone term
            regEx.Pattern = "\b" & targetWord & "\b"
            
            ' Check if the full word exists in the comment
            If regEx.Test(currentComment) Then
                ' Build the matched words string with clean separators
                If matchedWords <> "" Then
                    matchedWords = matchedWords & "; "
                End If
                matchedWords = matchedWords & targetWord
            End If
        Next wrdLp
        
        ' Write results to Column D
        Sht.Cells(CommentLp, "D").Value = matchedWords
    Next CommentLp
    
    ' Clean up objects
    Set regEx = Nothing
    Set Sht = Nothing
End Sub

Key Changes Explained: #

• Regex Word Boundaries: The \b in the pattern ensures we only match the target word when it’s surrounded by non-word characters (spaces, punctuation, or the start/end of the string). This stops partial matches like "ke" in "make" or "cel" in "excel".
• No Error Suppression: Instead of On Error Resume Next, we use regEx.Test() which returns a simple boolean (no errors thrown if the word isn’t found).
• Cleaner Output: We build the matched words string from scratch, so there’s no need to trim leading characters with Mid() like in the original script.
• Case Insensitivity: Keeps the same behavior as your original code, where lowercase "how" matches uppercase "How".

Example Test Result: #

If Column C contains:

How can i make excel to search word against a list of words?

And Column A has words: how, ke, cel, abcd, xyz

The script will write how to Column D (no partial matches for "ke" or "cel").

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