On Feb 27, 2:20*pm, Jack Deuce
wrote:
Gary,

That's what I was attempting to do. *To standardize St to St.
" St" is the full text. *I even added the preceeding space in front of
St but the problem is REPLACE replaces " Station St" as " St.ation
St." I was hoping there is a way to tell REPLACE to only FIND/REPLACE,
under certain circumstances, where the value is at the end of text.
There would be way too many parmateres to enter if I had to enter each
unique value. *I may as well change them manually.







On Mon, 27 Feb 2012 11:48:11 -0500, GS wrote:
After serious thinking Jack Deuce wrote :
We have many people entering addresses into the main database and by
the time we get them the addresses are not in any standard format,
i.e, Street, St, St., RR 1, Rt. 1, Rt 1, etc. *I'm creating a macro
that will examine known problems and change to the new standard but
have come across a problem when the search string ends with characters
that need to be changed. *Here's an example,

501 East Station St *as entered should convert to

501 E. Station St. * * *East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation

Selection.Replace What:=" St", Replacement:=" St.", LookAt:=xlPart, _
* * * * SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
* * * * ReplaceFormat:=False

This will only happen when the text is at the end of the string as I'm
checking for leading and following spaces on the other scans.

Hope this makes sense and someone can provide a work around.

thx

You should *always* use the full text of the string being replaced so
the function operates on only that text. So...

*Selection.Replace What:=" Street", Replacement:=" St.", _
* * * * *LookAt:=xlWhole, SearchOrder:=xlByRows, _
* * * * *MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

*Note* that it isn't necessary to include any parameters that contain
default values.

How many names total. If you want to send your file to dguillett1
@gmail.com I'll take a stab