Hi. I'm new to VBA, using excel 2003 (yes, I know....that's what company gave me) and trying to create a UDF to strip "stop words" from addresses. The udf works but for some reason not all words are being replaced? For example " Avenue " is not being replaced from string "199 Lee Avenue PMB 873". Replaces some words not others. could there be something in my data? I trimmed the strings.
1. is there a way to remove stop words that are at the end of the string, within the same udf?
2. is there a way to find/replace regardless of case, not have to include in my array all combinations " apt ", " Apt ", " APT "
Thank you.
Function RemoveAddrWordsV2(sInput As String) As String
Dim arr, I
arr = Array(" apartment ", " Apartment ", " Apartment ", " apt ", " Apt ", " APT ", " apmt ", " Apmt ", " APMT ", _
" ave ", " Ave ", " AVE ", " avenue ", " Avenue ", " AVENUE ", " bdlg ", " Bdlg ", " BDLG ", " blvd ", _
" BLVD ", " Blvd ", " boulevard ", " Boulevard ", " BOULEVARD ", _
" building ", " Building ", " BUILDING ", " court ", " Court ", " COURT ", " ct ", " Ct ", " CT ", _
" e ", " E ", " east ", " East ", " EAST ", " expressway ", " Expressway ", " EXPRESSWAY ", _
" expy ", " Expy ", " EXPY ", " expw ", " Expw ", " EXPW ", " floor ", " Floor ", " FLOOR ", " fl ", " Fl ", " FL ", _
" highway ", " Highway ", " HIGHWAY ", " highwy ", " Highwy ", " HIGHWY ", " hiway ", " Hiway ", " HIWAY ", _
" hiwy ", " Hiwy ", " HIWY ", " hway ", " Hway ", " HWAY ", " hwy ", " Hwy ", " HWY ", _
" Lane ", " lane ", " LANE ", " Ln ", " LN ", _
" n ", " N ", " north ", " North ", " NORTH ", " ne ", " Ne ", " NE ", " northeast ", " Northeast ", " NORTHEAST ", _
" nw ", " Nw ", " NW ", " northwest ", " Northwest ", " NORTHWEST ", _
" s ", " S ", " se ", " SE ", " Se ", " south ", " South ", " SOUTH ", " southeast ", " Southeast ", " SOUTHEAST ", _
" sw ", " SW ", " Sw ", " southwest ", " Southwest ", " SOUTHWEST ", _
" room ", " Room ", " ROOM ", " rm ", " Rm ", " RM ", " route ", " Route ", " ROUTE ", " rte ", " Rte ", " RTE ", _
" Road ", " road ", " ROAD ", " Rd ", " RD ", " sq ", " SQ ", " sqr ", " Sqr ", " SQR ", " sqre ", " Sqre ", " SQRE ", " squ ", " Squ ", " SQU ", _
" square ", " Square ", " SQUARE ", " st ", " St ", " ST ", " ste ", " Ste ", " STE ", _
" str ", " Str ", " STR ", " street ", " Street ", " STREET ", " strt ", " Strt ", " STRT ", _
" suite ", " Suite ", " SUITE ", " unit ", " Unit ", " UNIT ", " w ", " W ", " west ", " West ", " WEST ", _
" unit ", " Unit ", " UNIT ", " parkway ", " Parkway ", " PARKWAY ", " parkwy ", " Parkwy ", " PARKWY ", _
" pkway ", " Pkway ", " PKWAY ", " pkwy ", " Pkwy ", " PKWY ", " pky ", " Pky ", " PKY ", _
" pl ", " Pl ", " PL ", " place ", " Place ", " PLACE ")
For I = LBound(arr) To UBound(arr) Step 2
sInput = Replace(sInput, arr(I), " ")
Next I
RemoveAddrWordsV2 = sInput
End Function
1. is there a way to remove stop words that are at the end of the string, within the same udf?
2. is there a way to find/replace regardless of case, not have to include in my array all combinations " apt ", " Apt ", " APT "
Thank you.
Function RemoveAddrWordsV2(sInput As String) As String
Dim arr, I
arr = Array(" apartment ", " Apartment ", " Apartment ", " apt ", " Apt ", " APT ", " apmt ", " Apmt ", " APMT ", _
" ave ", " Ave ", " AVE ", " avenue ", " Avenue ", " AVENUE ", " bdlg ", " Bdlg ", " BDLG ", " blvd ", _
" BLVD ", " Blvd ", " boulevard ", " Boulevard ", " BOULEVARD ", _
" building ", " Building ", " BUILDING ", " court ", " Court ", " COURT ", " ct ", " Ct ", " CT ", _
" e ", " E ", " east ", " East ", " EAST ", " expressway ", " Expressway ", " EXPRESSWAY ", _
" expy ", " Expy ", " EXPY ", " expw ", " Expw ", " EXPW ", " floor ", " Floor ", " FLOOR ", " fl ", " Fl ", " FL ", _
" highway ", " Highway ", " HIGHWAY ", " highwy ", " Highwy ", " HIGHWY ", " hiway ", " Hiway ", " HIWAY ", _
" hiwy ", " Hiwy ", " HIWY ", " hway ", " Hway ", " HWAY ", " hwy ", " Hwy ", " HWY ", _
" Lane ", " lane ", " LANE ", " Ln ", " LN ", _
" n ", " N ", " north ", " North ", " NORTH ", " ne ", " Ne ", " NE ", " northeast ", " Northeast ", " NORTHEAST ", _
" nw ", " Nw ", " NW ", " northwest ", " Northwest ", " NORTHWEST ", _
" s ", " S ", " se ", " SE ", " Se ", " south ", " South ", " SOUTH ", " southeast ", " Southeast ", " SOUTHEAST ", _
" sw ", " SW ", " Sw ", " southwest ", " Southwest ", " SOUTHWEST ", _
" room ", " Room ", " ROOM ", " rm ", " Rm ", " RM ", " route ", " Route ", " ROUTE ", " rte ", " Rte ", " RTE ", _
" Road ", " road ", " ROAD ", " Rd ", " RD ", " sq ", " SQ ", " sqr ", " Sqr ", " SQR ", " sqre ", " Sqre ", " SQRE ", " squ ", " Squ ", " SQU ", _
" square ", " Square ", " SQUARE ", " st ", " St ", " ST ", " ste ", " Ste ", " STE ", _
" str ", " Str ", " STR ", " street ", " Street ", " STREET ", " strt ", " Strt ", " STRT ", _
" suite ", " Suite ", " SUITE ", " unit ", " Unit ", " UNIT ", " w ", " W ", " west ", " West ", " WEST ", _
" unit ", " Unit ", " UNIT ", " parkway ", " Parkway ", " PARKWAY ", " parkwy ", " Parkwy ", " PARKWY ", _
" pkway ", " Pkway ", " PKWAY ", " pkwy ", " Pkwy ", " PKWY ", " pky ", " Pky ", " PKY ", _
" pl ", " Pl ", " PL ", " place ", " Place ", " PLACE ")
For I = LBound(arr) To UBound(arr) Step 2
sInput = Replace(sInput, arr(I), " ")
Next I
RemoveAddrWordsV2 = sInput
End Function
