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?

Block if without end if
Number to words in excel without vba - Guide
Vba case like - Guide
How to delete whatsapp account without login - Guide
How to delete whatsapp account without phone - Guide
Delete snapchat - Guide

1 response

Answer 1 / 1

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

whitepanther
Aug 4, 2011 at 04:24 PM

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

whitepanther
Aug 4, 2011 at 04:26 PM

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...?

whitepanther
Aug 4, 2011 at 04:34 PM

Sorry I meant "college,new plymouth" - it's the comma..

whitepanther
Aug 4, 2011 at 04:48 PM

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"?

whitepanther
Aug 4, 2011 at 04:56 PM

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....

Similar discussions

How to delete fake Facebook account

How to remove fake Facebook account?

Delete Fake Facebook Account

How to delete my friend Facebook fake account?

Similar discussions

Subscribe To Our Newsletter!