Macro to remove word from text in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Hi, Mifty. Use this:

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

HTH,
Matthew Pfluger
 
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.
 
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
 
Back
Top