Redact Dollar Values in Tables

[T-Belle]

Once again, I need of help from the experts.


I have a document containing numerous tables. Many of thetables have dollar amounts that need to be redacted, meaning replace thenumbers following the dollar sign with X’s.

For example, $369,003 would be $XXX,XXX. The dollar amounts rangefrom the ones to the millions. Unfortunately, the values appear in differentcolumns depending on the table, so it is not possible to narrow the search to aspecific column, such as the last column.

I have code that will search for text in tables only, but Ido not know how to modify it to search for the “$” and replace each numberfollowing the $ with an X.

Is it possible to do what I have described above? I would begrateful for your help. Thank you in advance for considering my request.

[macropod]

Are there other numbers in the tables? Do they need redaction - or does it matter if they are? In any event, be aware that an X character quite possibly has a different width than any of the numerals.

[T-Belle]

Good evening, Paul.

Thank you for getting back with me. You bring up a good point about the column width. I had not thought of that. I appreciate your attention to detail. The financial tables are pretty tight, so with could be a factor. As such, instead of replacing each number following the dollar sign, I think it would be best to replace everything after the dollar sign with three X's; therefore, using my previous example, $369,003 would become $XXX.

The tables will contain columns/cells with other alphanumeric data, so I wouldn't be able to simply replace every number with an X. That would be too easy.

Again, thank you for the quick response. I hope I have provided the information you need.


T.

[macropod]

A fairly safe replacement, width-wise, would be to use asterisks. For example:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, StrTxt As String
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "$[0-9.,]{1,}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
If .Information(wdWithInTable) = True Then
StrTxt = .Text
For i = 0 To 9
StrTxt = Replace(StrTxt, i, "*")
Next
.Text = StrTxt
End If
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
End Sub
With this approach, the reader can at least appreciate the order of magnitude of the numbers concerned. If you don't want that, use something like:

If .Information(wdWithInTable) = True Then
.Text = "$XXX"
End If

[T-Belle]

Hey, Paul.

Thank you!!

The code for replacing numerals following a dollar sign with XXX is a success. It is fantastic! It will save countless hours of work. I cannot thank you enough. I'll go back a try code using asterisks as the replacement text now.

[T-Belle]

It worked like a charm, Paul! This is fantastic. Thank you.