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?

try this

Sub cleaner()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If InStr(1, rng, "text", vbTextCompare) > 0 Then
rng.Value = Clear
End If
Next
End Sub


On Oct 19, 2:27*pm, ppeer <trafo0...@gmail.com> wrote:
> 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?

Check your other post.

ppeer wrote:
>
> 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?

--

Dave Peterson

I'm pretty sure this single line of code will do what you want...

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

--
Rick (MVP - Excel)


"ppeer" <(E-Mail Removed)> wrote in message
news:4c112a88-70f5-491b-96e5-(E-Mail Removed)...
> 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?

I didn't see a previous post by this person.

--
Rick (MVP - Excel)


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Check your other post.
>
> ppeer wrote:
>>
>> 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?
>
> --
>
> Dave Peterson

Check...

From: ppeer <(E-Mail Removed)>
Newsgroups: microsoft.public.excel
Subject: delete cell if part of cell contains
Date: Mon, 19 Oct 2009 01:56:02 -0700 (PDT)


Rick Rothstein wrote:
>
> I didn't see a previous post by this person.
>
> --
> Rick (MVP - Excel)
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Check your other post.
> >
> > ppeer wrote:
> >>
> >> 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?
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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.

--
Rick (MVP - Excel)


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Check...
>
> From: ppeer <(E-Mail Removed)>
> Newsgroups: microsoft.public.excel
> Subject: delete cell if part of cell contains
> Date: Mon, 19 Oct 2009 01:56:02 -0700 (PDT)
>
>
> Rick Rothstein wrote:
>>
>> I didn't see a previous post by this person.
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Check your other post.
>> >
>> > ppeer wrote:
>> >>
>> >> 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?
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

On 19 okt, 19:48, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I'm pretty sure this single line of code will do what you want...
>
> Cells.Replace "*text*", "", xlPart, xlByColumns, False
>
> --
> Rick (MVP - Excel)
>
> "ppeer" <trafo0...@gmail.com> wrote in message
>
> news:4c112a88-70f5-491b-96e5-(E-Mail Removed)...
>
>
>
> > 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?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

thanks, this line of code gets the job done!

On 19 okt, 19:48, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I'm pretty sure this single line of code will do what you want...
>
> Cells.Replace "*text*", "", xlPart, xlByColumns, False
>
> --
> Rick (MVP - Excel)
>
> "ppeer" <trafo0...@gmail.com> wrote in message
>
> news:4c112a88-70f5-491b-96e5-(E-Mail Removed)...
>
>
>
> > 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?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

and I like the minimalized code!