Excel批量移除单元格列表指定词汇：支持公式/VBA与词汇编辑

Got it, let's work through this problem. Since the SUBSTITUTE function can't handle a dynamic, editable list of words to remove, we've got two reliable solutions for you: a no-code Excel formula approach (great for quick setups) and a VBA method (perfect for flexibility, especially with larger datasets or whole-word matching).

First, set up your exclude list for easy edits:

• Create a new worksheet named ExcludeList
• Paste all words you want to remove (like company, ltd, university, of, and) in column A, one word per row (start from A2—you can add a header in A1 if you want)

Then, in cell D2 of your Names worksheet (next to your first entity in C2), use this formula (works for Excel 365/2021 or later):

=TRIM(REDUCE(C2, FILTER(ExcludeList!$A:$A, ExcludeList!$A:$A<>""), LAMBDA(current_text, exclude_word, SUBSTITUTE(current_text, exclude_word, ""))))

How it works: #

• FILTER(ExcludeList!$A:$A, ExcludeList!$A:$A<>"") grabs all non-empty cells from your exclude list, so you can add/remove words anytime without adjusting the range manually
• REDUCE iterates over each exclude word, replacing it with an empty string in the original text
• TRIM cleans up any extra spaces left after substitutions

For older Excel versions (no REDUCE support), you can use a nested SUBSTITUTE—note this is less dynamic (you’ll have to update the formula if you add/remove words). Example for 5 words:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2, ExcludeList!$A$2,""), ExcludeList!$A$3,""), ExcludeList!$A$4,""), ExcludeList!$A$5,""), ExcludeList!$A$6,""))

If you need more control (like whole-word matching, case sensitivity, or batch processing), VBA is the way to go.

Option 2a: Custom Function for Individual Cells #

1. Press Alt + F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste this code:

Function RemoveExcludedWords(inputText As String, excludeRange As Range, Optional matchWholeWord As Boolean = False) As String
    Dim excludeWord As Range
    Dim resultText As String
    Dim regex As Object
    
    resultText = inputText
    
    If matchWholeWord Then
        Set regex = CreateObject("VBScript.RegExp")
        regex.Global = True
        regex.IgnoreCase = True ' Set to False if you need case-sensitive matching
    End If
    
    For Each excludeWord In excludeRange
        If excludeWord.Value <> "" Then
            If matchWholeWord Then
                regex.Pattern = "\b" & excludeWord.Value & "\b" ' \b = word boundary (ensures only full words are removed)
                resultText = regex.Replace(resultText, "")
            Else
                resultText = Replace(resultText, excludeWord.Value, "", , , vbTextCompare) ' vbTextCompare = case-insensitive
            End If
        End If
    Next excludeWord
    
    ' Clean up extra spaces left from substitutions
    RemoveExcludedWords = Trim(WorksheetFunction.Substitute(resultText, "  ", " "))
End Function

4. Go back to your Names worksheet, and in D2 enter:

=RemoveExcludedWords(C2, ExcludeList!$A$2:$A$21, TRUE)

• The TRUE parameter enables whole-word matching (so "company" won't be removed from "companyabc")—use FALSE if you want partial matches
• Add/remove words in ExcludeList column A, and the formula will update automatically

Option 2b: Batch Processing Macro #

If you want to clean all rows in column C at once:

1. In the same VBA Module, paste this macro:

Sub BatchCleanEntities()
    Dim wsNames As Worksheet
    Dim wsExclude As Worksheet
    Dim excludeRange As Range
    Dim lastRow As Long
    Dim i As Long
    Dim resultText As String
    
    ' Set your target worksheets
    Set wsNames = ThisWorkbook.Worksheets("Names")
    Set wsExclude = ThisWorkbook.Worksheets("ExcludeList")
    
    ' Get the full exclude list (from A2 to last non-empty row)
    lastRow = wsExclude.Cells(wsExclude.Rows.Count, "A").End(xlUp).Row
    Set excludeRange = wsExclude.Range("A2:A" & lastRow)
    
    ' Get last row of data in Names column C
    lastRow = wsNames.Cells(wsNames.Rows.Count, "C").End(xlUp).Row
    
    ' Process each row (skip header row if you have one)
    For i = 2 To lastRow
        resultText = wsNames.Cells(i, "C").Value
        For Each excludeWord In excludeRange
            If excludeWord.Value <> "" Then
                ' Use whole-word matching here—remove \b if you don't need it
                With CreateObject("VBScript.RegExp")
                    .Global = True
                    .IgnoreCase = True
                    .Pattern = "\b" & excludeWord.Value & "\b"
                    resultText = .Replace(resultText, "")
                End With
            End If
        Next excludeWord
        ' Clean up extra spaces
        resultText = Trim(WorksheetFunction.Substitute(resultText, "  ", " "))
        wsNames.Cells(i, "D").Value = resultText
    Next i
    
    MsgBox "All entities cleaned successfully!", vbInformation
End Sub

2. Run the macro by pressing Alt + F8, selecting BatchCleanEntities, and clicking Run. It’ll output the cleaned text to column D automatically.

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