VBA locate & delete words within a sentence

 whitepanther -

I know this is probably very easy but I'm having trouble figuring it out. I'm trying to find a code that will identify key words within a sentence (in one cell - always B3). The words being "for" and "parish" and delete them. I also need to locate the words New Plymouth (if they exist in that particular cell) and replace them with "NP".

I then have a code that I'm using to copy the info in B3 and paste it as the Sheet name - which is as follows:

ActiveSheet.Name = Range("B3").Text
Application.CutCopyMode = False

Any ideas?

1 reply

Registration date
Sunday June 14, 2009
Last seen
August 7, 2021
copy the string in B3 to M3 ( can be any empty cell in that case modify macro "undo"

try this macro test

the spellings in B3, M3 and in the macro should be exact -no unnecessary spaces

Sub test()
Dim r As Range, j As Integer
On Error Resume Next
Set r = Range("B3")
j = WorksheetFunction.Search("for", r)
r.Value = Left(r, j - 1) & Right(r, Len(r) - (j + 3))
j = WorksheetFunction.Search("parish", r)
r.Value = Left(r, j - 2)
j = WorksheetFunction.Search("new plymouth", r)
If IsNumeric(j) Then
r.Value = Left(r, j - 1) & "np"
End If
End Sub

Sub undo()
Range("M3").Copy Range("B3")
End Sub
Just an additional note sometimes the words New Plymouth don't have a space between words for example it might say "college,newplymouth" - not sure that makes a difference...?
Sorry I meant "college,new plymouth" - it's the comma..
Oop - one more thing. Just realised if the word parish is written like this "parish,Avalon" then it deletes ,Avalon along with it.. Is there a way to write the code so it only deletes "Parish"?
Sorry for all the notes! Ok so it's not just the comma that's the issue. It deletes anything after the word Parish and puts "np" at the end of each entry regardless of if the words "new Plymouth" are present or not. If the words 'New Plymouth" are present it deletes everything in the cell and replaces it with "np".... am very much a novice at this so am not sure how to tweek it to work....
Hi again - found a work around which I think is easier. Did a text to columns then searched along the row for the words to delete. Seems to work.

Thanks for your help though - this code may come in handy in the future :)