Our Excel add-in CDXZipStream includes a simple but powerful function called CDXZipCode, which provides a wealth of information for U.S. postal codes. Just right-click on any cell in your worksheet, and under “CDXZipStream Functions” select “Insert CDXZipCode Data”.
You’ll see an input box, where you can input either the ZIP Code itself or the worksheet cell location of the ZIP Code. Then select the data desired from the list. For example, if the ZIP Code of interest is in worksheet cell A2, and the city of that ZIP code is desired, use the following input:
Click “OK”, and the cell that was right-clicked will now show the city of the ZIP Code in cell A2. This cell will actually contain a custom function formula that retrieves the data from a local database, as shown below:
=CDXZipCode(A2,”City”)
This formula can be copied and applied to a long list of ZIP codes, such as in this example:
Since the data is provided through a custom function formula, you also have the option of inputting the formula directly to a cell. This is particularly useful when obtaining multiple data fields. Just use the name of the data field in each formula, such as:
=CDXZipCode(A2,”City”)
=CDXZipCode(A2,”County”)
=CDXZipCode(A2,”State”)
The available data fields for CDXZipCode Premium (with the CDXZipStream Demographics. Premium, and Premium ACS versions) cover a wide range of topics, such as household size and income, population, population by race, latitude, longitude, congressional distract, median age, mailing information, and various economic indicators. Mapping links are also available from Google and MapQuest. CDXZipCode Basic, which is available with CDXZipStream Population and lower versions, covers city, county, state, latitude and longitude only. A complete listing of all available data fields is shown at the end of this article. Definitions of all fields are provided in the downloadable Excel file CDXZipStream Data Feeds and Definitions.
To see the CDXZipCode function in action, please view the following short tutorial. (It can also be viewed on YouTube: Get City, County, and State from ZIP Codes)
Available data fields from the premium CDXZipCode function, available with all CDXZipStream demographic versions:
- AliasIntroDate
- AreaCode
- AsianPopulation
- AverageHouseValue
- BlackPopulation
- BoxCount
- BusinessAnnualPayroll
- BusinessEmploymentFlag
- BusinessFirstQuarterPayroll
- CarrierRouteRateSortation
- CBSA
- CBSA_DIV
- CBSA_Div_Name
- CBSA_Name
- CBSA_Type
- CBSADivisionPopulation
- Ciy
- CityAliasAbbreviation
- CityAliasMixedCase
- CityAliasName
- CityDeliveryIndicator
- CityMixedCase
- CityStateKey
- CityType
- ClassificationCode
- CongressionalDistrict
- CongressionalLandArea
- CountyANSI
- CountyFIPS
- CountyName
- CSA
- CSAName
- DaylightSaving
- DeliveryBusiness
- DeliveryResidential
- DeliveryTotal
- Divison
- Elevation
- FacilityCode
- FemalePopulation
- FinanceNumber
- GrowingCountiesA
- GrowingCountiesB
- GrowthIncreaseNumber
- GrowthIincreasedPercentage
- GrowthRank
- HawaiianPopulation
- HispanicPopulation
- HouseholdsPerZipcode
- IncomePerHousehold
- IndianPopulation
- LandArea
- Latitude
- Longitude
- MailingName
- MalePopulation
- Map - Google
- Map - Mapquest
- MedianAge
- MedianAgeFemale
- MedianAgeMale
- MFDU
- MSA
- MSA_Name
- MultiCounty
- NumberOfBusinesses
- NumberOfEmployees
- OtherPopulation
- PersonsPerHousehold
- PMSA
- PMSA_Name
- Population
- PopulationEstimate
- PreferredLastLineKey
- Region
- SFDU
- State
- StateANSI
- StateFIPS
- StateFullName
- TimeZone
- UniqueZIPName
- WaterArea
- WhitePopulation
- ZipCode
- ZIPIntroDate