 Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Extracting a State and a ZIP Code.

# Extracting a State and a ZIP Code by Allen Wyatt
(last updated June 25, 2018)

Dan has a column of cells and each cell contains three items: city, state and ZIP Code. (All three are in a single cell, much like you see in an address line.) Some of the ZIP Codes are five digits and some are nine. Dan needs to pull both the two-character state and the five-digit ZIP Code into their own cells, to the right of the current data. Dan knows he can use the Text to Columns tool, but feels that it involves a lot of work since he would need to deal with multiple-word city names and commas. Dan can't help but think there may be a formulaic approach that would be easier.

There needs to be a few assumptions made about the data in order to make any recommendations. Let's assume, for example, that all the data is in this format:

```My Town, CA 98765-4321
```

The portion from the dash onward (the trailing part of the ZIP Code) is optional, but the position of the comma is static—it always follows the name of the town—and the state always consists of two characters. In this case it is easy to devise two formulas that extract the state abbreviation and the first five digits of the ZIP Code:

```=MID(A1,FIND(",",A1)+2,2)
=MID(A1,FIND(",",A1)+5,5)
```

Both formulas key on the comma; it serves as a delimiter between the city and the two items really want. If there is no comma in the data or if there are multiple commas, then the formulas won't return the desired information.

The formulas also assume that there are no extra spaces in your data; at most there is a single space after the comma and between the state and ZIP Code. This is, of course, easy enough to enforce—just use Find and Replace to replace two spaces with a single space anywhere in your worksheet.

Of course, if your data is this structured, you can still rely on the Text to Columns tool to do your work. All you need to do is run the tool and split your data based on the comma. This will leave the city in one cell and put the state and ZIP Code together in the next cell. Then you can use Text to Columns again, this time on the second cell (not the city name) and divide the contents based on the space.

If your data is not that structured—perhaps it has multiple commas in the address or extra spaces—then an entirely different approach is called for. To deal with this the basic technique involves trimming the data to remove extraneous spaces (leading, trailing, and internal), then determining the location of the last space and the second-to-last space.

You can pull out the five digits in the ZIP Code, which is defined as immediately following the last space in the data, by using this formula:

```=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",
CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)
```

The two-character state abbreviation can be returned by pulling out the two characters immediately following the second-to-last space:

```=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)
```

If your data is even less structured—perhaps it includes addresses that don't all have two-character state abbreviations (N.J. instead of NJ)—then you would best be served to use a macro to divide up the data. The reason for this is that VBA has a much richer set of text handling functions than what you can do using Excel formulas. The following macro creates a user-defined function that can return either the state or ZIP Code:

```Function GetStateZIP(rstrAddress As String, iAction As Integer) As String
Dim arr As Variant
Dim sState As String
Dim sZIP As String
Dim J As Integer
Dim K As Integer

Application.Volatile
If Len(rstrAddress) = 0 Then Exit Function

sState = "?"
sZIP = "?"
For J = Len(rstrAddress) To 1 Step -1
If Mid(rstrAddress, J, 1) = " " And sZIP = "?" Then
sZIP = Mid(rstrAddress, J + 1, 5)
For K = Len(rstrAddress) To 1 Step -1
If Mid(rstrAddress, K, 1) = " " And sState = "?" Then
sState = Mid(rstrAddress, K + 1, 20)
End If
Next K
End If
Next J
If iAction = 1 Then
GetStateZIP = sState
End If
If iAction = 2 Then
GetStateZIP = sZIP
End If
End Function
```

To use this function, simply provide a cell reference and either 1 (if you want the state) or 2 (if you want the ZIP Code). Here is an example of requesting the ZIP Code for whatever address is in cell A1:

```=GetStateZIP(A1,2)
```

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9598) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Extracting a State and a ZIP Code.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Determining How Many Windows are Open

You can open multiple documents at the same time in Word, and each document occupies its own document window. Here's a ...

Discover More

Correcting a Capital Mistake

As you are entering data in a worksheet, Excel can monitor what you type and make corrections for common mistakes. One ...

Discover More

Converting Strings to Numbers

When creating macros, you often need to convert a text string that contains numbers into actual numeric values. You do ...

Discover More Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

Listing Combinations

You can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given ...

Discover More

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need ...

Discover More

Using Named Formulas Across Workbooks

You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 6?

2019-12-03 14:49:04

Mike

iI had a text entry in F2, containing a mix of Canadian and US postal codes (5 and 9 digit zips) and used the following to extract state or province, figuring where the last space would fall in the address to determine where the state would be:

=IF(LEFT(RIGHT(F2,4),1)=" ",LEFT(RIGHT(F2,10),2),IF(LEFT(RIGHT(F2,6),1)=" ",LEFT(RIGHT(F2,8),2),LEFT(RIGHT(F2,13),2)))

2016-09-13 02:49:47

Mary Caller

I came across this tip and it also explains a similar formula. https://www.sheetzoom.com/xlstools/tlln01 Maybe this can give you a clue.

2015-09-29 16:46:02

Gail

Great suggestions, but I just need to separate county and state...but there is no comma just a space....and some of the counties include a space like San Diego CA...etc.

so how can i separte into two columns, the county and then the state?

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.