Below is a list of the most common address formatting issues and their solutions
My addresses are split across multiple columns
The most common issue by far and there’s a whole very quick solution here.
4-digit US zipcodes missing a zero at the beginning
Sometimes when a zipcode start with a zero (like 08100) various versions of Excel will try to convert that to a real number and cut off the leading 0. This converts the zipcode to the non-existent 8100 and will throw off the geocoder. If you see a lot of un-geocoded addresses in the Northeastern US, look for this error.
Australian provinces often need to be spelled out
For some reason the geocoding services we use really struggle with the abbreviated Australian provinces like WA for Western Australia or VIC for Victoria. Doing a quick Find/Replace in your file before bulk uploading can really improve geocoding.
Including Suite number or Office number or “care of” or other non-address info in the address
Sometimes you get the data from your distributor and it with a bunch of extra stuff either before or (worse) in the middle of the address. The best thing to do is to ask the distributor to do a data export without that stuff included.
Our geocoders don’t handle PO Boxes particularly well. In any case that’s not actually somewhere you customers can visit so it probably makes sense to just delete those addresses.