Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Extracting a State and a ZIP Code

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.

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
    rstrAddress = Trim(rstrAddress)
    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)
            rstrAddress = Trim(Left(rstrAddress, J))
            For K = Len(rstrAddress) To 1 Step -1
                If Mid(rstrAddress, K, 1) = " " And sState = "?" Then
                    sState = Mid(rstrAddress, K + 1, 20)
                    rstrAddress = Trim(Left(rstrAddress, K))
                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)

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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Comments for this tip:

Greg Mouning    09 Jun 2014, 11:49
I tried to revise the macro but receive an error when the Find function is encountered. Could you review the following code and offers some assistance?

Function GetCityStateZIP(rstrAddress As String, iAction As Integer) As String
    Dim arr As Variant
    Dim sState As String
    Dim sZIP As String
    Dim sCity As String
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer

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

    sCity = "?"
    sState = "?"
    sZIP = "?"
    For J = Len(rstrAddress) To 1 Step -1
        If Mid(rstrAddress, J, 1) = " " And sZIP = "?" Then
            sZIP = Mid(rstrAddress, J + 1, 5)
            rstrAddress = Trim(Left(rstrAddress, J))
            For K = Len(rstrAddress) To 1 Step -1
                If Mid(rstrAddress, K, 1) = " " And sState = "?" Then
                    sState = Mid(rstrAddress, K + 1, 20)
                    rstrAddress = Trim(Left(rstrAddress, K))
                End If
            Next K
            For L = Len(rstrAddress) To 1 Step -1
                If Mid(rstrAddress, L, 1) = " " And sCity = "?" Then
                    sCity = Left(L, Find(",", L) - 1)
                    rstrAddress = Trim(Left(rstrAddress, L))
                End If
            Next L
        End If
    Next J
    If iAction = 1 Then
        GetCityStateZIP = sCity
    End If
    If iAction = 2 Then
        GetCityStateZIP = sState
    End If
    If iAction = 3 Then
        GetCityStateZIP = sZIP
    End If
End Function
Greg Mouning    09 Jun 2014, 11:11
Thanks for sharing these functions and macro. What about pulling out the city name? Is there anyway to include that in a seperate cell?
Juan    08 Jun 2014, 14:50
This article will be easier to understand if it includes a practice file in which I can try and analyze the long formula

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.