Another Find and Replace macro help please!

Posted by TJ on December 07, 2001 9:11 AM

I have a text file extracted from a CRM database that contains multiple instances of data that I need to replace. It spans over 25+ columns within excel and each column will contain unique data that needs to be replaced.
An example would be the address field. Two such replacements that I need to make are "St." or "St" to equal "STREET". There are many similar instances with things like "Ave", "Blvd","N.","S.","E.","W.", ect. I want to make everything comply with the USPS standards.

The next column will have another set of unique data to replace.

I know there has to be a simple solution to this, but I haven't been able to perform this in a more efficient manner.
THANK YOU FOR ANY HELP IN ADVANCE!

Posted by YANECKC on December 07, 2001 10:05 AM

LETS SAY ADDRESS COLUMN IS E

CREATE MACRO

Sub FINDREPLACE()
'
' FINDREPLACE Macro
' Macro recorded 12/7/2001 by JC14910
'

'
Columns("E:E").Select
Selection.Replace What:="ST", Replacement:="STREET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="STREET.", Replacement:="STREET", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End Sub

------------------------------
THEN HIGHLIGHT COLUMN E AND RUN NEW MACRO.

YANECKC

Posted by TJ on December 07, 2001 11:06 AM

Thank you very much for the suggestion. It looks similar to something that I had tried in the past, but didn't work because it didn't go through the whole column and only replaced the first instance. Would adding some type of loop statement make a difference in this macro?

Regarless, I am going to give it a shot and see what comes of it. If I have problems, I'll be back.

Thanks for your help!