delete cell if part of cell contains

  • Thread starter Thread starter ppeer
  • Start date Start date
P

ppeer

For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?
 
For Each cell In Activesheet.Usedrange

If cell.Value Like "Text*" then

cell.ClearContents
EndIf
Next cell
 
For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?
Click to expand...

This can be done with a VBA Macro.

But before using this, be sure to:

1. Backup your data
2. Understand the "Find" method options in case the word you want to search
for is not "text"

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

===========================
Option Explicit
Sub ClearCellsContainingText()
Do Until Cells.Find(What:="text", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False) _
Is Nothing

Cells.FindNext.Clear
Loop
End Sub
==============================
--ron
 
If your data is in a single column, you could apply data|Filter|autofilter to
that column.

Then use a custom filter contains (or starts with???) your text.

Then select the visible cells
Edit|Goto (or F5 or ctrl-g)|Visible cells only.
and hit the delete key on the keyboard to clear the contents of the visible
cells.
 
Ahh, okay, I see. Anyway, I still think either the code line I posted or the
equivalent Replace All from the menu bar would be good approach.
 
LOL.... I thought I was posting my message against your posting in the
programming newsgroup. Sorry.

--
Rick (MVP - Excel)


Rick Rothstein said:
Ahh, okay, I see. Anyway, I still think either the code line I posted or
the equivalent Replace All from the menu bar would be good approach.
Click to expand...
 
You should really post your message in only one newsgroup; otherwise you
risk alienating the volunteers who you are hoping will answer your question.
Why would you be alienating them? Well, if someone posts an answer in the
other newsgroup and a different volunteer spends time developing what turns
out to be the same answer in this newsgroup, they will be less likely to
want to help you in the future for fear they would just be wasting their
time again.

Anyway, I posted a VB solution to your question over in the programming
newsgroup where I first encountered your question. That answer was...

Cells.Replace "*text*", "", xlPart, xlByColumns, False

If you are not seeking a VB solution, then you can do the equivalent to this
line from the Excel menu bar. Click Edit/Replace on the menu bar, put *text*
(note the surrounding asterisks) in the Find field and leave the Replace
field empty, click the Options>> button if the extra options are not being
displayed and make sure both the "Match case" and "Match entire cell
contents" boxes are *not* checked, then hit the Replace All button.
 
Back
Top