Search and replace....

  • Thread starter Thread starter Kevin Stecyk
  • Start date Start date
K

Kevin Stecyk

Margaret,

You can replace "Dir " (excluding quotes). In essence, by leaving a space
after Dir, you should avoid your conflict. The same applies to Ave. Just
leave a space after Ave.

Regards,
Kevin
 
Is it possible to search for Dir, replace with Director,
but not replace Director with Directorector. Another
example is Ave. Search for Ave and replace with Avenue,
but it also replaces Avenue with Avenuenue. I know I can
then search for Avenuenue and replace with Avenue, but I
have 160 documents in which I have to eliminated
abbreviations in title and address fields, and it
tiresome.

Thanks.
 
Good suggestion, but it's not always the entire field --
might be Executive Director, might be ABC School District
where the sender has abbreviated Director as Dir or
District as Dist.
 
Good suggestion, but it's not always the entire field --
might be Executive Director, might be ABC School District
where the sender has abbreviated Director as Dir or
District as Dist.
Click to expand...
...

If you're willing to use VBA and you have a more or less standard Windows
configuration (either Internet Explorer 5 or higher installed or Windows
Me/2K/XP, which require some work *not* to install the Windows Script Host), you
could use the following macro.


'regex search and replace throughout selected range
'or Text property of selected object
Sub SearchAndReplace()
Dim c As Range, t As Variant, regex As Object, ans As Variant
Static mp As String, rp As String

Set regex = CreateObject("VBScript.RegExp")
regex.Global = True

ans = InputBox("Enter match pattern:", "Search & Replace", mp)
If ans = "" Then Exit Sub Else mp = ans

ans = InputBox("Enter replacement pattern:", "Search & Replace", rp)
If ans = "" Then _
If MsgBox("Blank replacement?", vbYesNo, "Search & Replace") = vbNo Then _
Exit Sub
rp = ans

Application.ScreenUpdating = False
Application.EnableEvents = False

regex.Pattern = mp

If TypeName(Selection) = "Range" Then
For Each c In Selection.Cells
t = c.Formula
t = regex.Replace(t, rp)
If Not (c.HasFormula And IsError(Evaluate(t))) Then c.Formula = t
Next c

Else
Selection.Text = regex.Replace(Selection.Text, rp)

End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


This allows you to use _regular expressions_, which are characters and character
combinations given special meanings that can be used to represent most text
patterns. To replace only Dir with Director when Dir isn't part of a longer
word, select the range you want to process, run the macro and enter

\b([Dd]ir)\b

in the first dialog displayed by the macro and

$1ector

in the second dialog displayed by the macro. See the following site for (terse)
instructions on how to use VBScript regular expressions.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsgrpRegExpSyntax.asp
 
Back
Top