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

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



In article ,
Mifty wrote:

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

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


"JE McGimpsey" wrote:

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



In article ,
Mifty wrote:

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

Hi, Mifty. Use this:

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

HTH,
Matthew Pfluger

"Mifty" wrote:

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

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.

In article ,
Matthew Pfluger wrote:

Hi, Mifty. Use this:

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

HTH,
Matthew Pfluger

"Mifty" wrote:

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

Thank you Matthew,

Just the job
Cheers
--
Mifty


"Matthew Pfluger" wrote:

Hi, Mifty. Use this:

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

HTH,
Matthew Pfluger

"Mifty" wrote:

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