[Mica]

Hey guys,

When you purchase lists or use AddressFinder, how do you mass-reformat addresses? The org I work for is very particular in their formatting. I use an access query for AddressFinder which is pretty much set... however the list I just purchased (and others in the past) have a different set of horrible formatting.

The series of queries I use is below:

SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(
[ADDRESS],
" E "," East "),
" W "," West "),
" N "," North "),
" S "," South "),
"Central Park West","Central Park W"),
"Central Park W","Central Park West"),
"Sutton Place South","Sutton Place S"),
"Sutton Place S","Sutton Place South"),
" Avenue "," Ave "),
" Ave "," Avenue "),
" Suite "," Ste "),
" Ste "," Suite "),
"# ","#"),
"#","# "),
" 1st "," First "),
" 2nd "," Second "),
" 3rd "," Third "),
" 4th "," Fourth "),
" 5th "," Fifth "),
" 6th "," Sixth "),
" 7th "," Seventh "),
" 8th "," Eighth "),
" 9th "," Ninth "),
" 10th "," Tenth "),
"Route 10 Apt","Route 10, Apt")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;
SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(
[ADDRESS],
"Apt ","Apt. "),
"Parkway Apt","Parkway, Apt"),
"Avenue Apt","Avenue, Apt"),
"NW Apt","NW, Apt"),
"NE Apt","NE, Apt"),
"East Apt","East, Apt"),
"West Apt","West, Apt"),
"North Apt","North, Apt"),
"South Apt","South, Apt"),
"Boulevard Apt","Boulevard, Apt"),
"West Penthouse","West, Penthouse"),
"South Apt","South, Apt"),
"Drive Apt","Drive, Apt"),
"Plaza Apt","Plaza, Apt"),
"Street Apt","Street, Apt"),
"Street Office","Street, Office"),
"Street Floor","Street, Floor"),
"Street Room","Street, Room"),
"Street Suite","Street, Suite"),
"Road Apt","Road, Apt"),
"Lane Apt","Lane, Apt"),
"Lane Unit","Lane, Unit"),
"PO Box","P.O. Box"),
"Court Apt","Court, Apt"),
"Place Apt","Place, Apt")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;
SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(
[ADDRESS],
"Street #","Street, Apt."),
"Avenue #","Avenue, Apt."),
"NW #","NW, Apt."),
"South #","South, Apt."),
"Boulevard #","Boulevard, Apt."),
"Broadway Front","Broadway, Front"),
" Fl "," Floor "),
"Place Apt","Place, Apt"),
"Square Penthouse","Square, Penthouse"),
"Court Unit","Court, Unit"),
"Terrace Apt","Terrace, Apt"),
"Avenue Floor","Avenue, Floor"),
"Square Apt","Square, Apt"),
"Mgmt","Management"),
"Road Unit","Road, Unit"),
"Street Floor","Street, Floor"),
"Avenue Floor","Avenue, Floor"),
"Drive #","Drive, Apt."),
"Broadway Apt.","Broadway, Apt."),
"Street Lobby","Street, Lobby"),
"Drive Unit","Drive, Unit"),
"Avenue Suite","Avenue, Suite")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;
SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(
[ADDRESS],
"Avenue Basement", "Avenue, Basement"),
"Avenue Lobby","Avenue, Lobby"),
"Avenue Penthouse","Avenue, Penthouse"),
"South Unit","South, Unit"),
"Point Apt","Point, Apt"),
"Way Unit","Way, Unit"),
"Street Penthouse","Street, Penthouse"),
"Avenue Room","Avenue, Room"),
"Circle Apt","Circle, Apt"),
"NE Unit","NE, Unit"),
"NW Suite","NW, Suite"),
" Ph "," Penthouse "),
" Penthouse ",", Penthouse "),
" St,"," Street,"),
" Uppr "," Upper "),
" Frnt"," Front"),
" Hl"," Hall"),
" Rvrside "," Riverside "),
" Drv"," Drive"),
" Pl "," Place "),
" Grand Street"," Grand St"),
" Grand St","Grand Street")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;

SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(Replace(Replace(
Replace(Replace(Replace(Replace(
[ADDRESS],
" Grand Street"," Grand St"),
" Grand St","Grand Street"),
" Rockefeller Plz"," Rockefeller Plaza"),
"Plaza Floor","Plaza, Floor"),
" Plz", " Plaza"),
" Street"," St"),
" St"," Street"),
" Rm "," Room "),
"Street Room","Street, Room"),
"Street Front","Street, Front"),
"Lbby","Lobby"),
"Broadway Room","Broadway, Room"),
"Broadway Floor","Broadway Floor"),
"Avnue","Avenue"),
"Amrcas","Americas"),
"Ln Room","Lane, Room")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;
SELECT AddressCleanup.ADDRESS,
Replace(Replace(Replace(Replace(Replace(Replace(
[ADDRESS],
"Park Avenue","Park Ave"),
"Park Ave","Park Avenue"),
"Fifth Avenue","Fifth Ave"),
"Fifth Ave","Fifth Avenue"),
"Union Square East","Union Square E"),
"Union Square E","Union Square East")
AS CnAdrPrf_Addrline2
FROM AddressCleanup;