VBA locate & delete words within a sentence

Solved/Closed
whitepanther - Aug 3, 2011 at 11:41 PM
 whitepanther - Aug 7, 2011 at 05:37 PM
Hello,

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.Select
ActiveSheet.Name = Range("B3").Text
Application.CutCopyMode = False

Any ideas?

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 4, 2011 at 12:17 AM
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
0
Hi thanks that worked great - except that if new plymouth doesn't exist then it replaces all the text in the cell with "np"!.... can't see why that might be...?

I also just realised I need to do the same with "Palmerston North" replacing it with "Palm.N"

I tried this but it came up with a compile error - Block If without End If...

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"
j = WorksheetFunction.Search("palmerston north", r)
If IsNumeric(j) Then
r.Value = Left(r, j - 1) & "Palm.N"

End If
End Sub
0
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...?
0
Sorry I meant "college,new plymouth" - it's the comma..
0
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"?
0
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....
0