Solved: Search and Replace lone % and $ signs

[AaronT]

Hello All,

I have a series of documents that contain spaces for staff to provide dollar and percentage amounts. The $ and % are pre-loaded as part of the template. In some cases these dollar and percentage amounts do not apply and dont get filled out. I am now left with a document containing many "lone" % and $ signs.

When I try to search and replace for %, "Match Whole Word Only" is disabled so I am not able to replace only the % signs where there is not numeric value that precedes it. Is there anyway around this?

Your assistance is always much appreciated! Thank you!

Aaron

[Tinbendr]

Interesting.... I cobbled a doc together, but could replace every $ and %.

Could they be fields?

Can you upload a stripped down version?

[AaronT]

Hi Tinbendr,

Thank you for the quick response! I've attached a typical example. I'm trying to replace the symbols only where there is not text before or after the symbols. I dont want to replace all of the symbols. Appreciate the help!

Aaron

3993

[gmaxey]

Try:

Sub ScratchMaco()
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "$"
While .Execute
If Asc(oRng.Characters.Last.Next) = 13 Then
oRng.Delete
oRng.Collapse wdCollapseEnd
End If
Wend
End With
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "%"
While .Execute
If Asc(oRng.Characters.Last.Next) = 13 Then
oRng.Delete
oRng.Collapse wdCollapseEnd
End If
Wend
End With
End Sub

[AaronT]

Hi Greg,

Almost! It works great for the dollar signs but it seems to be deleting all of the percentage signs. Perhaps a distinction needs to be made since % usually follows the text instead of preceding? Thoughts?

Thank you so much for the quick help! This is something that has been annoying me for months.

Aaron

[gmaxey]

Doh!! My fault. Yes you would need to look at the preceeding character in that case:

Sub ScratchMaco()
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "$"
While .Execute
If Asc(oRng.Characters.Last.Next) = 13 Then
oRng.Delete
oRng.Collapse wdCollapseEnd
End If
Wend
End With
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "%"
While .Execute
If Asc(oRng.Characters.First.Previous) = 13 Then
oRng.Delete
oRng.Collapse wdCollapseEnd
End If
Wend
End With
End Sub

[AaronT]

You are the man! Thank you, Greg! Amazing!:beerchug:

[gmaxey]

Since in this case you are using a table, you could also just look at the cell content. Something like this:

Sub ScratchMacoII()
Dim oTbl As Word.Table
Dim oCell As Cell
Set oTbl = ActiveDocument.Tables(1)
For Each oCell In oTbl.Range.Cells
If Len(oCell.Range.Text) = 3 Then
Select Case Left(oCell.Range.Text, 1)
Case "$", "%"
oCell.Range.Text = ""
End Select
End If
Next oCell

[AaronT]

Awesome! The first versio works very well...but I'll definitely keep this version too. Thanks again, Greg!