Assuming you have a column of addresses in an Excel spreadsheet and you want to extract the state and ZIP code from each address:
- Create two new columns in your spreadsheet, one for the state and one for the ZIP code.
- 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.
- 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.
- 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.