Written by Allen Wyatt (last updated June 18, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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)
Note:
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
In mathematics, the sum of a range of sequential integers, starting with 1, is known as a triangular number or Gaussian ...
Discover MoreThere are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...
Discover MoreWhen you subtract two numbers from each other, you have a certain expectation of what Excel should deliver. What if you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-08-29 09:51:49
Willy Vanhaelen
I don't know what went wrong with my second last post but all the line feeds are gone which makes it almost unreadable. But it became less important now because I managed to reduce the GetStateZip UDF to only 2 lines of code:
Function GetStateZIP(V As Variant, Action As Integer)
V = Split(Application.Trim(V))
GetStateZIP = Left(V(UBound(V) + Action - 2), 5)
End Function
Yes, this tiny macro does the same job as the one in this tip, try it!
Second parameter: 1 for state and 2 for ZIP code.
2021-08-27 09:49:03
Willy Vanhaelen
Here is a much smaller version of the UDF (userdfined function) that does the same job:
Function GetStateZIP(S As String, iAction As Integer) As String
Dim X As Integer, Y As Integer, Z As String
S = Application.Trim(S)
X = InStrRev(S, " ")
Y = InStrRev(S, " ", X - 1)
Select Case iAction
Case 1: GetStateZIP = Mid(S, Y + 1, X - Y)
Case 2: GetStateZIP = Mid(S, X + 1, 5)
End Select
End Function
2021-08-27 09:43:08
Willy Vanhaelen
The UDF (user defined function) in this tip is rather complicated. To look simply for a space twice, 2 reversed loops are used. In this case it’s a good idea to use instead the VBA InStrRev function provided for that purpose.When GetStateZIP is invoked, it calculates both the ZIP code and the State initials which isn’t necessary because each time only one of those is needed.Here is my small version that does the same job:Function GetStateZIP(S As String, iAction As Integer) As StringDim X As Integer, Y As Integer, Z As StringS = Application.Trim(S)X = InStrRev(S, " ")Y = InStrRev(S, " ", X - 1)Select Case iActionCase 1: GetStateZIP = Mid(S, Y + 1, X - Y)Case 2: GetStateZIP = Mid(S, X + 1, 5)End SelectEnd FunctionThere is a difference between VBA’s Trim function and that of Excel. The VBA Trim removes only leading and trailing spaces while Excel’s Trim removes all spaces from text except for single spaces between words. So, when you expect there may be more than one space between parts of a string, which is the case for this UDF, then Excel’s Trim must be used: Application.Trim(....
2021-08-26 11:21:38
Willy Vanhaelen
The UDF (user defined function) in this tip is way to complicated. To search simply for the position of a space twice, 2 reversed loops are used although VBA provides a function for that purpose: InStrRev.Each time GetStateZIP is invoked, it calculates both the ZIP code and the State initials although only one of those can be returned which is a waste.Here is my version (less than half the size) that does the same job equally well:Function GetStateZIP(S As String, iAction As Integer) As StringDim X As Integer, Y As Integer, Z As StringS = Application.Trim(S)X = InStrRev(S, " ")Y = InStrRev(S, " ", X - 1)Select Case iAction Case 1: GetStateZIP = Mid(S, Y + 1, X - Y) Case 2: GetStateZIP = Mid(S, X + 1, 5)End SelectEnd FunctionThere is a difference between VBA’s Trim function and that of Excel. The VBA Trim removes only leading and trailing spaces while Excel’s Trim removes all spaces from text except for single spaces between words. So, when you expect there may be more than one space between parts of a string, which is the case for this UDF, then Excel’s Trim must be used: Application.Trim(....
2021-08-25 12:52:29
Lin
Hi Allen Wyatt,
Thank you so much for the zip code formula.
It's work and safe me hundreds of hours.
Lin
2020-06-30 15:13:55
Daryl
Relating to Extracting State, Zip Code Or City From an Address In Excel.
I'm working with data where people sometimes enter the city, state and zip in the address line. The delimiter is a space for all information in the address line.
I have code that works if the city and state are only one word. The issue arises when the city and/or state are two or more words.
e.g. New Mexico, South Carolina etc... for state
e.g. New Haven, New London etc... for city
When a person types the information sometimes there is just the street address sometimes there is also the city, state and other times there is also the city, state and zip. There is no consistency in what is on the address line.
Any thoughts?
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?
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments