Macro to remove word from text in a cell

[Guest]

Hi Everyone,

I posted recently to ask how to add a word to a line of text in a cell using
a recorded macro that would run on a number of workbooks where the text was
slightly different but needed "provisional adding at the end.

Gary's Student kindly provided this

Select the cells and run:

Sub prov()
For Each r In Selection
r.Value = r.Value & " Provisional"
Next
End Sub

Works brilliantly !!!

BUT now I need to remove the "Provisional" from the end of the line of text.
Wouldn't you know it

Any ideas would be brilliant
Thanks

 

[JE McGimpsey]

One way:

Public Sub RemoveProvisionalFromEndOfCellText()
Const sSearch As String = "* Provisional"
Dim rCell As Range
Dim nLen As Long
If TypeOf Selection Is Range Then
nLen = Len(sSearch) - 1
With Selection
On Error GoTo ErrorExit
For Each rCell In Intersect(.Cells, _
.Parent.UsedRange.SpecialCells( _
xlCellTypeConstants, xlTextValues))
With rCell
If .Text Like sSearch Then _
.Value = Left(.Text, Len(.Text) - nLen)
End With
Next rCell
End With
End If
ErrorExit:
End Sub

 

[Guest]

Hi, Mifty. Use this:

sub removeProv()
For each r in selection
r.value = replace(r.value," Provisional","")
next
End Sub

HTH,
Matthew Pfluger

 

[JE McGimpsey]

While that may be exactly what the OP needs, note that it doesn't
discriminate between " Provisional" found at the beginning or middle of
a block of text, and the OP's specified end of the line of text.

 

[Guest]

Thank you Matthew,

Just the job
Cheers

 

[Guest]

Thank you JE,

I love your website.

I wasn't really expecting to get an answer on this one and thought I was
going to have to trawl through all the workbooks manually deleting it. So
thank you to both JE and Matthew.

I'm always amazed by the generosity and also the different approaches and
although I'm just scabbling on the bottom rung I'm finding these forums a
fantastic learning resource. Problem is that the more I learn the more I
realise just how little I know.

Cheers