Find and Replace

[Toonigh]

I have a column of road names with abbrevations for words like "Circle" is
"Cr".
there are over 75,000 total road names. If I do a replace on "Creekside Cr"
it changes it to "Circleeekside Circle" How do I "Replace all" without
changing the first word?

 

[Jacob Skaria]

The logic depends on what is to be replaced. For the example given below

Find what " Cr" (a space followed by Cr)
Replace with " Circle" (a space followed by Circle)

If this post helps click Yes

 

[Jarek Kujawa]

put a space before "Cr" in Find What
and a space before "Circle" in Replace With

HIH

 

[Eduardo]

Hi,
Highlight the range with the information, hit CTRL H, where it says find
what enter CR , and replace with enter Circle, then click on replace all

 

[David Biddulph]

.... but of course you'd sstill have a problem with "Jacob's Creek Drive", or
whatever.
--
David Biddulph

put a space before "Cr" in Find What
and a space before "Circle" in Replace With

HIH

 

[David Biddulph]

.... and in what way does that avoid changing the first word, Eduardo?

 

[Ron Rosenfeld]

I have a column of road names with abbrevations for words like "Circle" is
"Cr".
there are over 75,000 total road names. If I do a replace on "Creekside Cr"
it changes it to "Circleeekside Circle" How do I "Replace all" without
changing the first word?

What you need is a mechanism of determining that you only want to search for
"whole words". Excel's Find and Replace does not do this; neither do the
worksheet functions have an option for "whole word".

You can precede your abbreviation with a <space>, but this won't help with
strings like "Jacob's Creek Drive" as David submitted.

You could write a UDF (user defined function) or a Macro to do the conversion,
and test for whole words that way.

For example:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells to be processed. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Be sure to backup your data first, as the macro will change the data "in
place".

You can extend the Find/Replace options by adding pipe-delimited abbreviations
to the sFind string; and extending the Select Case construct appropriately.

========================================
Option Explicit
Sub FindReplaceWholeWord()
Dim sFind As String, sReplace As String
Dim c As Range, rng As Range
Dim re As Object, mc As Object

sFind = "\b(Dr|Cr|Ave|Av|St)\b"

Set rng = Selection
Set re = CreateObject("vbscript.regexp")
re.Global = False
re.ignorecase = True
re.Pattern = sFind
For Each c In rng
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
Select Case mc(0)
Case Is = "Dr"
sReplace = "Drive"
Case Is = "Cr"
sReplace = "Circle"
Case Is = "Ave", "Av"
sReplace = "Avenue"
Case Is = "St"
sReplace = "Street"
Case Else
sReplace = "Error"
End Select
c.Value = re.Replace(c.Value, sReplace)
End If
Next c

End Sub
=====================================
--ron