Using multifind and replace code for whole words

[MichaelHurd]

I've been using everyone's favorite multi find and replace code for the past 2 months and it's been wonderful, but it is still not exact enough. I put it into an addin and tied that to a button so now I just click it select the text I want it to replace and then select a table I have in another workbook that has 2 columns in it listing the words I want it to find and the words it needs to replace.

Here's the code:
<code class="vb keyword" style="font-style: inherit; margin: 0px !important; padding: 0px !important; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; font-size: 1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Sub</code><code class="vb plain" style="font-style: inherit; font-weight: inherit; margin: 0px !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; font-size: 1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: inherit !important; color: black !important; background: none !important;">MultiFindNReplace()</code><code class="vb comments" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 130, 0) !important; background: none !important;">'Update 20140722</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Dim</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Rng </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">As</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Range</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Dim</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">InputRng </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">As</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Range, ReplaceRng </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">As</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Range</code>
<code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">xTitleId = </code><code class="vb string" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: blue !important; background: none !important;">"KutoolsforExcel"</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Set</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">InputRng = Application.Selection</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Set</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">InputRng = Application.InputBox(</code><code class="vb string" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: blue !important; background: none !important;">"Original Range "</code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">, xTitleId, InputRng.Address, Type:=8)</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Set</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">ReplaceRng = Application.InputBox(</code><code class="vb string" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: blue !important; background: none !important;">"Replace Range :"</code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">, xTitleId, Type:=8)</code>
<code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">False</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">For</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Each</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Rng </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">In</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">ReplaceRng.Columns(1).Cells</code>
<code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Next</code>
<code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: black !important; background: none !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">True</code>
<code class="vb keyword" style="font-style: inherit; margin: 0px !important; padding: 0px !important; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; font-size: 1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">End</code><code class="vb keyword" style="font-style: inherit; margin: 0px !important; padding: 0px !important; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; font-size: 1em !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: inherit !important; color: rgb(0, 102, 153) !important; background: none !important;">Sub</code>
The problem is that it is still limited by how Excel performs Find and replace, so I've had to adjust my table to prevent the more glaring mistakes, but there's only so much I can do.

The task I have to do is Address abbreviations for street names and city names. eg. "100 Mountain Avenue North, Grand Harbor" should become "100 MTN AVE N, GR HBR". These are insurance bordereau that need to be uploaded to a DOS application and I'm going through thousands each month.

The big problem is that it is finding words within words to replace, so I've had to make adjustments in my table, by putting a space before or after the word, but that only fixes so much. eg 1. "Mont[space] to prevent "Edmonton" from becoming "EdMTon" while still capture "Mont Royal" to become "MT Royal". eg 2. "[space]Road" to prevent "Broadway" becoming "BRDway". The biggest problem are the cardinal directions that can be all over the place and even at the end of the text so I can't skirt that issue by playing with spaces in the replace table.

I'm wondering if the code could be adapted where it will only replace whole words that it finds. So it would change "1 Avenue West" to "1 Ave W", but leave "10 Western TR" instead of changing it to "10 Wern TR", etc.

May I please have some help? I think I need to take the input range and split it by spaces to get the whole words in an array, but not sure how to go about that.

Thanks so much!

 

[Fluff]

How about adding a space before & after each word in your list of words to find?

 

[MichaelHurd]

That won't work because sometimes I'm wanting to replace the first word or the last word. It would also make a very long list.
eg. I want to change 1 Avenue West which might also show up as West 1 Avenue depending on the addressing format. How could I capture that West and change it to W without needing to change something like Westminster to Wminster.

I've been adding those exceptions as I find them, so I do have change Wminster to Westminster in the row underneath change West to W. But there's still things slipping through and I'd rather find a more accurate way.

Again, I've got probably 20,000 addresses that I go through each month that need to be standardized and I've got a QA team running queries to make sure it is done to their standards. I have my personal opinions on the matter, but I'd rather avoid getting another list of 6000 address corrections from the last few months that need to be entered manually because I didn't abbreviate things their way.

 

[inactiveuserps07]

Michael, if your routine included:

1. first adding a space to the beginning and ending of each original address

2. including a space before and after each word and replacement in the legend

3. running replacement

4. TRIMming the final result to delete the beginning and ending spaces added to the address in step one

... might this work as you expect?

 

[Rick Rothstein]

Give this a try... replace your existing macro with the following macro and function. The function can be used in future projects of yours, but in the case of the code below, the MultiFindNReplace macro will repeatedly call the InStrExact function as it finds the exact text as stand alone text and replace it. There is no need for you to modify the text being searched nor the words being searched for with surrounding spaces... text being searched for does not have to be surrounded by spaces as the InStrExact function is able to find stand alone words even if they are next to punctuation marks or brackets.

[table="width: 500"]
[tr]
	[td]Sub MultiFindNReplace()
  Dim RI As Long, CI As Long, RR As Long, CR As Long, Pos As Long, xTitleId As String
  Dim Rng As Range, InputArr As Variant, ReplaceArr As Variant
  xTitleId = "KutoolsforExcel"
  Set Rng = Application.InputBox("Original Range ", xTitleId, , Type:=8)
  InputArr = Rng
  ReplaceArr = Application.InputBox("Replace Range :", xTitleId, Type:=8).Resize(, 2)
  For RR = 1 To UBound(ReplaceArr, 1)
    For RI = 1 To UBound(InputArr, 1)
      For CI = 1 To UBound(InputArr, 2)
        Pos = InStrRev(InputArr(RI, CI), ReplaceArr(RR, 1), , vbTextCompare)
        Do While Pos
          If InStrExact(InputArr(RI, CI), ReplaceArr(RR, 1), Pos) = Pos Then
            InputArr(RI, CI) = Left(InputArr(RI, CI), Pos - 1) & ReplaceArr(RR, 2) & Mid(InputArr(RI, CI), Pos + Len(ReplaceArr(RR, 1)))
          End If
          If Pos = 1 Then
            Pos = 0
          Else
            Pos = InStrRev(InputArr(RI, CI), ReplaceArr(RR, 1), Pos - 1, vbTextCompare)
          End If
        Loop
      Next
    Next
  Next
  Rng = InputArr
End Sub

Function InStrExact(ByVal SearchText As String, ByVal FindMe As String, _
                    Optional ByVal Start As Variant = 1, _
                    Optional ByVal MatchCase As Variant = False) As Long
  Dim X As Long, Str1 As String, Str2 As String, Pattern As String
  If TypeName(Start) = "Boolean" Then
    MatchCase = True
    Start = 1
  End If
  If MatchCase Then
    Pattern = "[!A-Za-z0-9]"
  Else
    SearchText = UCase(SearchText)
    FindMe = UCase(FindMe)
    Pattern = "[!A-Z0-9]"
  End If
  For X = Start To Len(SearchText) - Len(FindMe) + 1
    If Mid(" " & SearchText & " ", X, Len(FindMe) + 2) Like Pattern & FindMe & Pattern Then
      InStrExact = X
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

Note: There is a lot of processing going on with this routine, so if you have a lot of data, it may take a while for the code to finish... but the results should be perfect once it is done. I tried to make the code as efficient as possible, hopefully that will have helped some.

 

[MichaelHurd]

THANK YOU RICK!

First run through it does exactly what I needed. I will still be using both this code and the old code for my job, but it has made the task easier. The exact one is for the bulk of words that need to be changed, but I still need to use the regular one to take out periods and number suffixes like 21st avenue.

Again thank you so much!

 

[Rick Rothstein]

THANK YOU RICK!

First run through it does exactly what I needed.

You are quite welcome... I am glad it worked for you. Truth be told, I found this to be a fun problem to work on, so thank you for posting it (I am a programming nerd, what can I say).

...but I still need to use the regular one to take out periods and number suffixes like 21st avenue.

I can make you a ordinal suffix remover... that would be easy... but would need some additional clarifications

1) Did you want to remove only periods or all punctuations? If all, what about parentheses and dashes and the like?

2) For the suffixes (ignoring the other substitutions you indicated you would make), are you saying you would want something like this...

123 West 34th Street

to become

123 West 34 Street

and this...

Cor. 6th Ave and W34th St

to become...

Cor. 6 Ave and W34 St

 

[MichaelHurd]

It's all . (periods) that they want gone. So "St." or "Ave." would become "St" and "Ave". The other punctuation can stay as there generally isn't too much and it's how I catch whether I need to separate out for a Suite # or they stuffed multiple addresses into one cell.

For the numbers it's like you said where "4th avenue" would become "4 Ave". But it would also need to catch "124th Avenue".

Finally if you want to be really nice to me you could give me the code that would implement this If statement I'm using as a workaround.

=IF(RIGHT(a3,3)=" ST",REPLACE(a3,LEN(a3)-2,3," Street"),a3)

The QA team wants every variation of Saint abbreviated as ST, but they want Street to remain Street. However, sometimes the broker will have already abbreviated the address and the QA team want me to unabbreviate it.

So "Saint Andrews St" should become "St Andrews Street". Another example would be "Ste. Catharine St." would become "St Catharine Street".

You've already helped me out a lot!

 

[Rick Rothstein]

Finally if you want to be really nice to me you could give me the code that would implement this If statement I'm using as a workaround.

=IF(RIGHT(a3,3)=" ST",REPLACE(a3,LEN(a3)-2,3," Street"),a3)

The QA team wants every variation of Saint abbreviated as ST, but they want Street to remain Street. However, sometimes the broker will have already abbreviated the address and the QA team want me to unabbreviate it.

So "Saint Andrews St" should become "St Andrews Street". Another example would be "Ste. Catharine St." would become "St Catharine Street".

Do you ever get your starting text in the cell like this...

St. Andrews St.

If so, what might the most complicated cell text including that version of the address look like? I am asking because I would need to have the code decide that the first St. is the abbreviation for Saint and the second one for Street, but I need to know what other text could be in such a cell that might interfere with the code making that decision.

Besides the above, I just thought of a roadway in Philadelphia which might screw things up. How would you change this address for your work...

123 Street Road

Street is the actual name of the street and I don't think replacing it with ST would be correct, but since I don't know your full requirements, I figured I'd ask.

 

[MichaelHurd]

I was wrong in that QA doesn't want -(hyphens) in the city names. Really tough with how Quebec city names work. I've been keeping hyphens as they help me see if I need to add a suite # or if there are multiple addresses on one cell. The hyphens then get taken out as I clean those cells up.

Just so we're clear I need to run the MultiFind and Replace on "Street Address", "City" that I receive from the broker. If there is property coverage I need to split the "Street Address" into "Suite #", "Street #", and "Street Name" that needs to be in the abbreviated format. I use Left and Right functions with the first [space] to split the Street Address out and then filter through the list to make sure the format makes sense and correct any rows that have a Suite #, etc.

I haven't yet seen a St. Andrews St. yet, but that's why I first take away the periods and then look for cells that end with [space]St to change into street. I've yet to come across an address that ends in Saint. The wrinkle comes in if it is "St Andrews St South", which would become "St Andrews St S" and would really need to be "St Andrews STREET S" according to the QA team.

I'm not sure how I would catch a ST that should be Street when it is in the middle other than looking for "[space]ST[space]N/E/S/W". Somehow the QA team is catching them in their mapping correction reports. In the last report with about 3000 corrections about 100 were the ST in the middle ones. That report was the June one, so I'm hoping there's a big improvement when they run August, which is when I started using the multifind macro. I am still miffed at the hundreds of corrections to change "Sault Ste Marie" to "Sault ST Marie", not to mention all the Quebec names.

For your second example "123 Street Road" would become "123 Street RD". I have come across "Avenue Road" and things like "West Avenue". Those would become "Ave RD" and "W Ave" respectively. I have come across a city that has both a West Avenue and an Avenue W. When I asked the QA team about that and how we'd distinguish them for that it didn't seem to matter to them, especially as we'd have a postal code if it came to that. The abbreviations were more important in getting it into the system and then bundling up the reports to send on up to the reinsurers, etc..

Hope that provides some clarification on what is keeping me employed. I'm just glad I'm not afraid to use some Excel hacks to help speed up the work compared to my co-workers.