PC*MILER|Spreadsheets includes a number of functions that allow you to retrieve location information from the PC*MILER database.


Getting the City Name from a ZIP/Postal Code

=CityName (ZIPCode)


CityName returns the city name and state or country abbreviation corresponding to the given postal code. (Canadian Postal Codes, SPLC, and 3-digit U.S. ZIP codes representing centralized areas are valid with this function, as are international postal codes for PC*MILER|Worldwide customers.) CityName returns -1 if the postal code you entered is invalid.



Getting the ZIP/Postal Code from a City Name

=ZIPCode (City,State)


ZIPCode returns the postal code corresponding to any U.S. place name. The place name is a city with a state abbreviation (e.g. "Austin, TX’). ZIPCode returns a -1 if the place name you entered is invalid.


The ZIPCode function can be used to test if a given number is a valid postal code. For example, the following Excel command will return TRUE if cell B2 contains a valid postal code and FALSE if it does not: =NOT(EXACT(-1,ZIPCode(B2)))  



Neither postal codes outside of the U.S. nor SPLC can be derived from this function. However, Canadian and Worldwide codes can be found using the Location Lookup dialog. The steps to access the Location Lookup vary depending on the version of Excel you are using:


Excel 2003

In Excel 2003, select Insert menu > PC*MILER Location Lookup…, then enter a city/state (example: ‘Montreal, QC’) and click Lookup. All postal codes will be listed for the entered location. Click OK to close the dialog (the selected code won’t be entered, this must be done manually).

 

Excel 2007 or 2010

Click the Add-Ins tab and select PC*MILER Location Lookup…, then enter a city/state (example: ‘Montreal, QC’) and click Lookup. Click OK to close the dialog (the selected code won’t be entered, this must be done manually).


Getting the Full ZIP/Postal Code and City Name

=FullName (CityNameOrZipCode)


FullName returns the postal or SPLC code, city/state abbreviation, and county name corresponding to the given location. Any place name, postal code, or SPLC in the PC*MILER database may be used (Canadian Postal Codes included in PC*MILER, and international postal codes are additionally available for PC*MILER|Worldwide customers). FullName returns -1 if the PC*MILER place you entered is invalid.


TIP: If a Canadian city, Canadian postal code, or SPLC is entered in the function, it will return the city/state without a code.