Make sure this is all in the field named Address. Nothing is in city, st, or zip.
Pull down the Edit menu to Command, then run this command:
Code: Select all
replace all address with reduce(address)
replace all address with ALLT(address)+" "+ALLT(city),city with ""
use in iif(used('_uszips'),select('_uszips'),0)
replace zip with allt(substr(address,rat(" ",allt(address)))),;
address with left(address,rat(" ",allt(address)));
for empty(zip) and isdigit(right(allt(address),1))
replace address with allt(address)+" "+zip,;
zip with "" for isalpha(allt(zip))
public comalias
comalias=alias()
use (addbs(xDefFldr)+"dmm\_uszips.dbf") in 0 order uszip
replace city with _uszips.prefcity,;
st with _uszips.st for seek(left(zip,5),'_uszips')
use in iif(used('_uszips'),select('_uszips'),0)
replace address with left(address,rat(left(city,4),upper(address))-1) ;
for !empty(city) and rat(left(city,4),upper(address))>0
replace city with allt(substr(address,rat(" ",allt(address),2))) for (empty(city) ;
or allt(st) $ address)
replace address with strtran(address,allt(city)) for allt(city) $ address and !empty(city)
replace address with allt(address)+" "+city for !" " $ allt(address)
replace city with "" for allt(city) $ address and !empty(city)
release comalias