Keyword Search & Column Isolation

[drmingle]

Thanks in advance for any help. I have two primary questions:

1. What can I add/modify to the code below to isolate the search to a column instead of my entire worksheet?

2. How can I have the replacement text only replace the variation if it is stand alone?

(For example, 123 Village Court = 123 VLG CRT (vs) 123 Main Nashville, TN = shoud result in 123 Main Nashville, TN instead it is 123 Main NashVLGe, TN ).

Sub Normalizer()
'
' 'PURPOSE: Perform USPS.com postal standardization on data.
' 
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.75pt; WIDTH: 688pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=917 border=0><TBODY><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VILL”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VILLAG”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VILLAGE”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VILLG”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VILLIAGE”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #c0c0c0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c0c0c0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c0c0c0; WIDTH: 688pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c0c0c0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=917>[COLOR=windowtext]Cells.Replace What:=“VLG”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False<o:p></o:p>[/COLOR]

</TD></TR></TBODY></TABLE>
<o:p></o:p>
End Sub

 

[VoG]

For 1

Columns("C").Replace What:=“VLG”, Replacement:=“VLG”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

2. I don't really understand

 

[drmingle]

Vog:

1. works perfect thanks.

2. My issue is that I have cities with certain keyword in the medial of the word (i.e. Vill is in the center of the city Nashville). Currently my code searches for Vill and has no regard if it is a stand alone word or in the middle of a word.

I need the program to search stand alone words not a word within a word. otherwise completely good city names become garbely gook (i.e. Nashville becomes NasVLGe).

Any ideas?

 

[marilynjane]

Try LookAt:=xlWhole instead of LookAt:=xlPart...

That's my best guess!