Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find text within string and then delete
I want to look for the word 'report' within a larger text string and then delete all the text in the cell. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find text within string and then delete
Hi,
This works on column A. Right click your sheet tab, View code and paste this in and run it. Sub stance() Dim MyRange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "REPORT") Then c.ClearContents End If Next End Sub Mike "scubadiver" wrote: I want to look for the word 'report' within a larger text string and then delete all the text in the cell. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find text within string and then delete
Also this
With Sheets("Sheet1") Set c = .Cells.Find(what:="REPORT", LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do c.ClearContents Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With "Mike H" wrote: Hi, This works on column A. Right click your sheet tab, View code and paste this in and run it. Sub stance() Dim MyRange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "REPORT") Then c.ClearContents End If Next End Sub Mike "scubadiver" wrote: I want to look for the word 'report' within a larger text string and then delete all the text in the cell. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find text within string and then delete
thanks. I didn't need all the code but it helped. "Mike H" wrote: Hi, This works on column A. Right click your sheet tab, View code and paste this in and run it. Sub stance() Dim MyRange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each c In MyRange If InStr(1, UCase(c.Value), "REPORT") Then c.ClearContents End If Next End Sub Mike "scubadiver" wrote: I want to look for the word 'report' within a larger text string and then delete all the text in the cell. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find text within string and then delete
If InStr(1, UCase(c.Value), "REPORT") Then
The InStr function has two optional arguments... the first which is a starting position for the search (InStr is an unusual function in its having an optional *first* argument) and, if the first argument is specified, a fourth which allows you to make it perform a non case sensitive search. So, using your UCase approach as shown above, the function call could have been simplified to this... If InStr(UCase(c.Value), "REPORT") Then but using the first and fourth arguments would allow us to eliminate the UCase function call... If InStr(1, c.Value, "Report", vbTextCompare) Then where the letters in the word "Report" can be in any case (here I use the more friendly looking, at least to me, "proper" case). -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete part of a text string | Excel Worksheet Functions | |||
Function to read text file; find string; delete same before import | Excel Programming | |||
Delete text in a string marco help | Excel Programming | |||
Find part of string & delete row | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |