如何删除包含指定字符串的单元格全部内容？而非仅替换目标字符串

If you’ve tried basic Find & Replace and only removed the target string (leaving prefixes like Text1/Text2), here are several straightforward ways to fully clear any cell that contains your specific text (e.g., "XXX"):

Method 1: Use Filter (Excel/Google Sheets) #

This is the simplest no-code approach:

• Select the range of cells you want to check (e.g., Column A).
• Turn on the Filter feature:
  • In Excel: Go to the Data tab > click Filter.
  • In Google Sheets: Go to Data > Create a filter.
• Click the dropdown arrow on the column header > hover over Text filters (Excel) or Filter by condition (Google Sheets) > select Contains.
• Enter your target string (e.g., "XXX") and click OK. Only cells containing "XXX" will be visible.
• Select all visible cells (click the first visible cell, hold Shift, then click the last one).
• Press the Delete key to clear their entire content.
• Turn off the filter to see your updated dataset.

Method 2: Helper Column with Formula #

Use a formula to flag and replace cells containing the target string:

For Excel: #

• Insert a new column next to your data (e.g., Column B).
• In cell B1, enter:

  =IF(ISNUMBER(SEARCH("XXX", A1)), "", A1)
  

• Drag the fill handle down to apply the formula to all rows. This leaves cells without "XXX" as-is, and empties cells that contain "XXX".
• Select the entire helper column (Column B), copy it (Ctrl+C), then right-click the original column (Column A) > select Paste Special > Values to overwrite the original data with the cleared values.
• Delete the helper column afterward.

For Google Sheets: #

• Insert a new column, then in cell B1 enter:

  =ARRAYFORMULA(IF(ISNUMBER(SEARCH("XXX", A:A)), "", A:A))
  

• This automatically applies the formula to the entire column.
• Select Column B, copy it, right-click Column A > Paste special > Paste values only.
• Delete the helper column when done.

Method 3: VBA Macro (Excel Only) #

If you need to do this repeatedly, a macro can save time:

• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste the following code (replace "XXX" with your target string and adjust the range as needed):

  Sub ClearCellsContainingString()
      Dim targetRange As Range
      Dim cell As Range
      Dim searchString As String
      
      searchString = "XXX" ' Replace with your target string
      Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A100") ' Replace with your range
      
      For Each cell In targetRange
          If InStr(1, cell.Value, searchString, vbTextCompare) > 0 Then
              cell.ClearContents
          End If
      Next cell
  End Sub
  

• Press F5 to run the macro, or assign it to a button for easier access.

All these methods will fully clear any cell that contains your specified string, not just remove the string itself.

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