Excel Guides

Extracting a State and a ZIP Code in Excel

Assuming you have a column of addresses in an Excel spreadsheet and you want to extract the state and ZIP code from each address:

  1. Create two new columns in your spreadsheet, one for the state and one for the ZIP code.
  2. In the first cell of the state column, type =RIGHT(A1,2). This formula will extract the last two characters from the cell in column A. Be sure to replace A1 with the cell containing the address you want to extract the state from.
  3. In the first cell of the ZIP code column, type =LEFT(A1,5). This formula will extract the first five characters from the cell in column A. Be sure to replace A1 with the cell containing the address you want to extract the ZIP code from.
  4. Click on the first cell in the state column and then drag down to apply the formula to all cells in that column. Do the same for the ZIP code column.

You should now have two columns containing the state and ZIP code for each address in your original column.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.