delete text from cell after a certain word

[nervous_pilchard]

Hi, i have a spreadsheet with 100's of rows of data.

I have found a piece of code that removes text from a cell after the symbol ">" which is as follows:

Sub test()
Dim x As String
With ActiveSheet.UsedRange
    x = .Address
    .Value = Evaluate("if(" & x & "<>"""",if(isnumber(find("">""," & x _
        & ")),left(" & x & ",find("">""," & x & ")-1)," & x & "),"""")")
End With
End Sub

What i require is a slight modification so that it removes all text in the cell onwards from the word "is" - eg.

This is what the cell contains:
Windows Service SurfControl Web Filter Version 5.5 Service Pack 2 Service is Up

I require the following to be displayed:
Windows Service SurfControl Web Filter Version 5.5 Service Pack 2 Service

The reason i want the deletion from the word "is" because there are many different phrases that the cell contains after the word 'is' - e.g. is Up, is Down, is Critical etc etc..

Of course a find and replace option isnt suitable due to the volume of data. A piece of code is ideal so that it can be linked to a macro.

Any advice?

[arthurbr]

Of course a find and replace option isnt suitable due to the volume of data.

I don't see why? Selecting the data then Ctrl+H

Find what: is*
Replace with: (leave blank)
Replace all

Does it take that long?

[Marcol]

Perhaps it is not an issue but

Find what: is * ("is" space "*")

Might be safer

[DonkeyOte]

Purely for sake of Evaluate demo. you could modify to:

.Value = Evaluate("IF(ISTEXT(" & x & "),LEFT(" & x & ",FIND("" is "","" ""&" & x & "&"" is "")-2),REPT(" & x & ",1))")

again this assumes to always look for <space>is<space> to preclude false positives
(we add " is " at the end of each string to avoid need for error handling)

[arthurbr]

Marcol, as far as I know, the space doesn't matter, as the wildcard takes care of it

[Marcol]

@ arthurbr
The point I was making might be illustrated by this string.

"Windows Issue 2 SurfControl Web Filter Version 5.5 Service Pack 2 Service is Up"

Should it return
"Windows "
or
"Windows Issue 2 SurfControl Web Filter Version 5.5 Service Pack 2 Service "

Perhaps that will never occur but similar issues might arise.

Probably
Find what: is * (space "is" space "*")

as Don suggests is even more precise

[DonkeyOte]

edit: below composed prior to above edit - relates to the "is *" example:

Even then that approach is not watertight, for example:

"This is great isn't it?"

The Evaluate route allows OP to append string such that we can reduce risk of false positives

" This is great isn't it? is "

And fire a LEFT against it based on " is "

Though searching for " is *" would resolve the above the Find/Replace would fail should "is" appear as either last or first word in the sentence whereas the Evaluate would succeed*

*assuming you removed case sensitivity via an outer LOWER call - missing in my example!

[DonkeyOte]

slightly more robust Evaluate below - added a TRIM & LOWER call

.Value = Evaluate("IF(ISTEXT(" & x & "),TRIM(LEFT(" & x & ",FIND("" is "","" ""&LOWER(" & x & ")&"" is "")-1)),REPT(" & x & ",1))")

I suspect however that in this instance the Find/Replace will work just fine!

[Marcol]

Agreed Don, you posted as I was editing to clarify the point.

[nervous_pilchard]

thanks guys. interesting debate going on! love it.......