Excel - how to delete cells that don’t contain certain text?
I have a spreadsheet with certain text in many columns and rows. The text may appear by itself or as part of a word, meaning ABC or ABC123. I want Excel to find all instances of cells that DON'T have this term and clear the contents of the cell. Can I do this? I found a way to do it with filtering, but I have to do it one column at a time, which is a very long process doing it this way. Thanks for your help
More Questions From This User See All
Answers & Comments
Verified answer
Here is one way to do as you wish. Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
For Each cell In ActiveSheet.UsedRange
If InStr(UCase(cell), "ABC") Then
Else
cell.ClearContents
End If
Next
Target.Offset(0, 1).Select
End Sub
Select the worksheet containing the data to evaluate and right click the sheet tab at the bottom.
Select 'View Code'.
Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').
Close the VBE (red button w/white 'x' - top right).
Double click any cell to delete the contents of all cells in the sheet not containing ABC, abc, Abc, or even ABc.