Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Shortening ZIP Codes.

Shortening ZIP Codes

by Allen Wyatt
(last updated December 13, 2014)


In the United States, ZIP Codes come in two formats: five-digit and nine-digit. (Actually, the five-digit ZIP Code is a subset of the nine-digit ZIP Code.) If you are an Excel worksheet that contains address information, you may want to convert nine-digit ZIP Codes to their five-digit equivalent.

This is a rather easy task to accomplish, since all you need to do is strip everything after the fifth digit in the ZIP Code. Follow these steps:

  1. Insert a new column, just to the right of the existing ZIP Code column.
  2. Assuming the ZIP Codes are in column G and you added a new column H, you can enter the following in cell H3:
     =Left(G3, 5)
  1. Copy this formula into all the appropriate cells of column H.
  2. Select the entire column H.
  3. Press Ctrl+C. Excel copies the entire column to the Clipboard.
  4. Display the Home tab of the ribbon.
  5. Click the down-arrow under the Paste tool and choose Paste Special from the resulting choices. Excel displays the Paste Special dialog box. (See Figure 1.)
  6. Figure 1. The Paste Special dialog box.

  7. Make sure the Values radio button is selected.
  8. Click on OK. Column H has now been transformed from formulas into the formula results.
  9. Delete column G.

If you have an empty column to the right of your ZIP Codes, you can also use Excel's Text to Columns feature:

  1. Select all the cells that contain your ZIP Codes.
  2. Display the Data tab of the ribbon.
  3. Click the Text to Columns tool, in the Data Tools group. Excel displays the first step of the Convert Text to Columns Wizard. (See Figure 2.)
  4. Figure 2. The first step of the Convert Text to Columns Wizard.

  5. Make sure the Delimited option is selected, then click on Next.
  6. Select the Other check box, place a single dash in the box just to the right of Other, then click on Next.
  7. In the Column Data Format area, select Text. (You want your ZIP Codes to be formatted as text so that you don't lose any leading zeros.)
  8. Click on Finish.

At this point you have the first five digits of the ZIP Code in the original column and the last four digits (if any) in the previously empty column to the right. You can delete the column containing the four digits, if desired.

If you need to truncate ZIP Codes quite often, you may be more interested in a macro-based approach. The following macro will do the trick:

Sub ZIPShorter()
    For Each cell In Selection
        cell.Value = Left(cell.Value, 5)
End Sub

All you need to do is select the cells containing the ZIP Codes, and then run the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10768) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Shortening ZIP Codes.

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. ...


Pasting a Comment into Your Document

When developing a document, you may end up with all sorts of comments that you need to deal with. One common task is to copy ...

Discover More

Changing to the Right Thesaurus

Ever want Word to display a thesaurus for your country's version of English? This tip explains how to find the different ...

Discover More

Summing Digits in a Value

Want to add up all the digits in a given value? It's a bit trickier than it may at first seem.

Discover More

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!

More ExcelTips (ribbon)

Reordering Last Name and First Name

If you've got a list of names in a column, you may want to change the order of each name. For instance, the name have the ...

Discover More

Incrementing Numeric Portions of Serial Numbers

If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric portion ...

Discover More

Getting a Conditional Count of Cells Containing Values

Excel provides several worksheet functions that can be used to count cells containing values—particularly numeric ...

Discover More

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

View most recent newsletter.


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 8Mpixels. 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 three more than 5?

2017-04-15 02:14:38

Alan Elston

Hi Willy,
Yeh, probably my post was too long .. Lol.. often happens to me. But it is difficult to get enough information across to help someone understand. I thought I noticed you yesterday viewing some of my posts at ExcelForum. Rick was there as well, but not sure what he was looking at.
In some of my posts there I tackle the things we discussed here in a bit more detail, and with graphical explanations which help a bit.

But anyway I think we have it both fairly well in the bag now.

It was great that Rick popped by. I really appreciate that. Getting another insight into his thinking was very useful. I see now, for example, his reason for the Replace. I notice now in many of his codes that he has done similar things to reduce multiple concatenations. I find the insights into how people like Rick think incredibly useful.

I was very glad / relieved that he thought the If({1},___ ) was OK. I was nervous about that I had suggested it to you as I had not seen it done in this sort of code line before.
I expect that will be my standard now in place of where I usually did the standard ones like
Index(___ , 0 , 0 )
If(Row(), ___ )
If(Column() , ___ )
Initially it seems to work in all the cases where I have used the other alternatives.
My thoughts and theories on these things suggested it should, so that is a relief ! .. Lol...
Have a great Easter, Willy, you too Rick if you ever pop by again, and many thanks again for that great enlightenment

_.....”... logical thinking powered by urgent tendency of simplifying things,,,,” I can identify with that one :-)

2017-04-14 14:04:05

Willy Vanhaelen

Hi Rick,
Thanks for the compliment. It all started with finding it strange to replace an empty cell with an empty string. So I tried to replace IF(@="""","""",___ with IF(ROW(@),___ because ROW always returns an positive number (TRUE) and it worked. Then I experimented by removing the @ and it still worked (at that moment I didn't know why). So there was now only one place where the @ had to be replaced which gave me the idea to insert the Selection.Address directly into LEFT and discarding the Replace which resulted in my final code:
Selection = Evaluate("IF(ROW(),LEFT(" & Selection.Address & ",5))").
So it's just a matter of logical thinking powered by my urgent tendency of simplifying things :-)

Hi Alan,
As for your failing post, perhaps the reason is its length? Anyhow, I read it on and found it very interesting although I must admid that some parts are beyond my level. Thanks to you though I got deeper into the matter and now have a better insight of arrays.

2017-04-13 07:50:16

Alan Elston

Hi Rick, Hi Willy,
I tried to post a reply to Rick’s last Post but it would not post: So for now I have dropped it off here:
and in another spare Forum post.
I also have it in a text file here:
I will try to post later again if I can

2017-04-12 15:10:14

Rick Rothstein

I did get your email message Alan....

First off, my apologies to Alan for my causing him many frustrating hours that he has spent pulling apart my one-liners. Second, kudos to Willy for finding that shorter one-liner. As it turns out, the code line can be made even shorter...

Selection = Evaluate("IF({1},LEFT(" & Selection.Address & ",5))")

Since there has been quite a lot of discussion about my code and the use of the Evaluate function, let me talk about this for a moment. First, it is not my intention to confuse things with my use of the Replace function and the @ symbol (I have also used the # symbol as well). I use Replace in order to avoid multiple concatenations and I do that in order to make it easier (for me at least) to construct the formula argument for the Evaluate function directly as I write it. My original code line was this...

Selection = Evaluate(Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address))

I use the @ symbol as a stand-in for an address that I either know (as in this case) or have calculated earlier. I find it easier to create the formula that way then not using the Replace function which would require multiple concatenations like this...

Selection = Evaluate("IF(" & Selection.Address & "="""","""",LEFT(" & Selection.Address & ",5))")

Now it is true that Willy's, and the above, shorter code lines reference the address only once making concatenating the address directly into the formula argument less objectionable. By the way, when the address the output is going to is different than the address being evaluated, I tend to use the full address with a # sign replacing the row number for the end of the range instead. For example, if instead of using a Selection, the range had been fixed at A1 down to the last data item in Column A, and the output was to go to Column B (leaving the original data intact, then I would have written my original code line this way...

Range("B1:B" & LastRow) = Evaluate(Replace("IF(A1:A#="""","""",LEFT(A1:A#,5))", "#", LastRow))

instead of this way...

Range("B1:B" & LastRow) = Evaluate(Replace("IF(A1:A" & LastRow & "="""","""",LEFT(A1:A" & LastRow & ",5))", "#", LastRow))

I just find it easier to construct the formula part of the Evaluate argument without having to work around multiple concatenations. Of course, the above shorter version of the code could have been used to do this as well...

Range("B1:B" & LastRow) = Evaluate(IF({1},LEFT(A1:A" & LastRow & ",5))")

Now lets discuss a little bit about how the Evaluate function itself works. The natural tendency would have been to write the original code line this way...

Selection = Evaluate("LEFT(" & Selection.Address & ",5)")

but that does not work. Why? Underneath it all, the Evaluate function is an array-processing function and works just like an array-entered worksheet formula does except you do not have to commit the formula using CTRL+SHIFT+ENTER the way an array-enterd formula has to be committed... the Evaluate function, in effect, does that automatically for you behind the scenes. And that is the beauty of the Evaluate function... it returns an array of results which you can use VB code to manipulate... this gives us a tremendous amount of power as the underlying Excel formula processing engine that the Evaluate function taps into is quite fast. So, back to that last code line (where we try to process the LEFT function by itself). As it turns out, none of the text string processing functions appear to be array aware on their own, so you cannot get Evaluate to directly return an array of values from a text string function; however, if you embed that text string function within another function that is array aware, then it seems to induce array awareness in the text string function. That is where the IF function comes in... we just need it to process an array of values which, in turn, induces the text string function to produce an array of values to the IF function which, in turn, returns an array of values to the Evaluate function. Willy's ROW() function call does that as does the single element array constant ({1}) that I posted above. My usual fallback for the IF function used this way is to test for the iterated cells in the array for being empty and, if so, to return the empty string... usually this suppresses zeroes from being returned by the Evaluate function when an empty cell is encountered; however, since the LEFT function naturally returns an empty text string when the value it is processing is empty, that test for emptiness was not required (which is why ROW() or the single-element array constant that Willy and I proposed could be used).

2017-04-12 10:26:25

Willy Vanhaelen

Hi Alan,
A while ago I tried with 1 and TRUE but of course that doesn't work, not being an array. Putting 1 in curly braces is a very clever solution. It feels like fooling Excel and I like it.


P.S. It's a good thing keeping learning.

2017-04-11 03:38:11

Alan Elston

Hi Willy,
I am glad you looked into this, it turned out to be good learning. .

As far as I can see you have the answer, and I cannot see any extra advantage in this case of Rick’s alternative. ( His would return an empty explicitly for an empty cell, whereas yours would do so indirectly as the formula LEFT(____,5) will not error if the cell is empty , - it will return an empty again. So in effect they do the same )

I agree I would go finally with your solution, unless I felt like arranging that if the cell was empty , then the formula would write words to that effect in the cell.



Seeing and understanding the other alternatives is, as we have seen, a great way to learn.
Shame Rick did not stop by. I dropped him an Email, but he is very busy helping and answering in Forums

P.s. I was just using another trick in a Forum answer_..
_.. that one would allow you to simplify your formula a little further:

= Evaluate("=IF({1},LEFT(A1:A10,5))")

2017-04-10 11:25:03

Willy Vanhaelen

Hi Alan,

You are right, Rick's solution as well as my variants work practically equally well, even Allan Wyatt's loop does the job just fine. But trying to simplify code is a hobbyhorse of mine: "simplicity is the ultimate sophistication" (Leonardo da Vinci). For me, making a macro to do a job is the first step. Once it works I always try to simplify it :-)

The Watch Window is quite an interesting tool. I rarely use it because most of the time I can see the result in a variable in the tooltip by hovering over it with the mouse cursor but I didn't know you can analyse arrays in this way. I must remember this :-)


2017-04-09 03:18:20

Alan Elston

Hi Willy,
Yep , I spent a bit of time trying to figure out what the “@” was about as well!!! – I was thrown off, as @ is a Wildcard thing used in a Find replace Thingy. I should have guessed though , - as Rick often uses unusual character for no particular reason. I always make a point of trying to make it obvious what is going on, hence my alternative of “AnyFinkULike” .. Lol..

“.....I was thinking that instead of first inserting the parameters and then replace the value in the cell by the result it would be more direct....”........I may have misunderstood what you are saying there. You and Rick in fact are doing almost exactly the same. If you follow the order of things as I explained in my last post , then you will see that Rick is just doing that Replace stuff to get this final formula to evaluate:
That is quite close to yours.
This is a bit closer still, a sort of “half way house” between Rick’s and yours:
Rick is , again, just making it look more complicated then it is or needs to be. Your formula_..
_.. does exactly the same

“...."IF(ROW(),___ is working without the reference parameters to the array because Excel seems to be tolerant sometimes. Obviously ROW()'s array "follows".......” .....That is more or less what all my experiments suggest. In some cases it can be a bit more complicated. I have some very long detailed Blogs on it. I have never seen anyone else tackle this theme definitively in detail.. , so I did.

Very briefly, and a bit simplified:.. - once multi value analysis kicks in , then the following rule applies: Excel will keep going, that is to say keep returning values in an output Array to match the size and orientation of the largest dimensions of all Arrays and / or ranges in the total formula.

So for example, if you change your solution slightly to this_.....

Sub MultiValueFromSingleValueFunctionStuff() '
Dim Arr() As Variant
Let Arr() = Evaluate("IF(Row(A1:A11),LEFT(A1:A10,5))") ' Run code in Debug, F8 mode, stop at End Sub. - Highlight either Arr - Hit Shift+F9 - OK You will see in Watch window that Arr is a (1 to 11, 1 to 1) array. The last value is an error
Let Range("B1").Resize(UBound(Arr(), 1), UBound(Arr(), 2)).Value = Arr() ' Increase size of range to match that of array and assign the array values to the range
End Sub

_.. you will see that Excel has tried to do the evaluation over 11 “rows”. The last value returned in Arr(11, 1) will be an error as you had no value available in LEFT(A1:A10,5) when it did its 11th run.


2017-04-08 11:20:19

Willy Vanhaelen

@Alan: "The original code from Rick looked a bit more complicated then it was, or at least I was mistakenly reading into it more than was there."
I entirely agree with you and it had the same effect on me. Now that I finally "got it" it is in fact simple but I was "distracted" all the time by this @ which I thought could have a special meaning.

But while trying to decipher Rick's method recently (again :-), I was thinking that instead of first inserting the parameters and then replace the value in the cell by the result it would be more direct to replace it right away. So I came up with the following:
Selection=Evaluate("IF(ROW(),LEFT(" & Selection.Address & ",5))")
and it worked just fine.
"IF(ROW(),___ is working without the reference parameters to the array because Excel seems to be tolerant sometimes. Obviously ROW()'s array "follows" the array of the main element in the IF function: LEFT(SomeAddress,5) so it isn't necessary to add the reference range parameter to ROW().

2017-04-07 08:50:45

Alan Elston

Hi Willy,
Thanks I get it , - I expect thanks to you...
It is always difficult to get things across in the confines of a Comment post.
I expect we may be saying the very same thing.
I was thinking that Rick was doing something more clever with a complete address, and changing a 9 number zip to a 5 number zip and maintaining the full address. That was wrong. You are correct – Rick’s code is doing exactly what Allen’s is. ( And as often, preparing a comment or Forum post often results in you solving the problem yourself.. LOl.. – The “Power of Posting” !!! - I swear by it, ( and occasionally swear when I don’t get it either.. lol.. ) )

What his code actually does is simply returns the first 5 characters of a Selection just as you say.

This is my explanation, and I expect it is exactly what you are saying as well ...

Within the VBA Evaluate method argument, you may include VBA things. These things will be done ( evaluated but not by the Evaluaet(“ “) , rather just by VBA ) , first.
The result they give will then become part of the final string that VBA finally evaluate.

Also, even in a “one liner” , VBA code, things will actually be done ( evaluated ) in a pre determined order. ( Arguments within a Function , for example, which are themselves a Function or formula or VBA things will, be evaluated first if need be )

So this is Rick’s code progression

_ 1. Selection.Address is evaluated first - - In your suggested example it returns $A$1:$A$10

_ 2. The Replace is then done in this form:
Replace("IF(AnyFinkULike="""","""",LEFT(AnyFinkULike,5))", "AnyFinkULike", “$A$1:$A$10“)
so we will then have that Replace Function returning us this:

_.3. The Evaluate(“ “) is now done on that last string above
The main important thing that is done in that string is

As I expect you have gathered , these sort of simple functions like the VBA Strings Left Function, can be given a multi value argument instead of the usual single value. Then, whilst having “available in them” a number of values to be evaluated, they will not usually do multivalent evaluations.
We need some trick, that is to say we add something to the total formula which makes multi value evaluation take place, but which does not affect the overall result.
At each multi value evaluation a different value from those “available in them” is taken. Correspondingly the output is returned in an array which is dimensioned to reflect the evaluations being gone through in the conventional multi evaluation order of “along all columns then down to next row” ( In our case for a single column it just “goes down the rows”)
The trick I think you have used somewhere was the If(Row(),____)
Rick is suing a slightly different one
Your trick will give an array output over a dimension equal to the range in LEFT($A$1:$A$10,5), which will be (1 to 10, 1 to 1 ).
Ricks will give an output over a dimension equal to whichever is the bigger of the range in LEFT($A$1:$A$10,5) or the range in If(IF($A$1:$A$10="""","""",_____) , which in this example is actually the same. So similarly, a (1 to 10, 1 to 1 ) array is returned.

I think we both got it eventually. Just as you say, Ricks final code is just doing exactly what Allen’s code does

The original code from Rick looked a bit more complicated then it was, or at least I was mistakenly reading into it more than was there.


2017-04-06 11:33:51

Willy Vanhaelen

I am also not familiar with US ZIP codes but that is not important. Just fill some cells with 5 and 9 digit numbers, that's were it's about

In my opinion Rick's line of code does exaclly the same as Allen Wyatt's loop because, if for example A1:A10 is selected, the immediate window evaluates it to an array formula:

But while composing this comment it suddenly became clear to me.
In Rick's code:
1) "IF(@="""","""",LEFT(@,5))" is the "expression" part of VBA's Replace function.
2) "@" is the Substring being searched for
3) Selection.Address is the Replacement substring
and voilà :-)

2017-04-05 16:07:35

Alan Elston

Hi, I might possibly have an idea what is going on..
But the problem is I have no idea about the United States, ZIP Codes and typical house addresses. Can you give me a few complete addresses. Make them up if you like, but give some with the typical five digit and some with the typical nine digit ZIP code. Make sure some of them are exactly the same address, but in the different five or nine digit ZIP code convention.
I have a feeling Rick is doing something a bit different to what Allen Wyatt was doing. But I need some typical data to check.

2017-04-04 11:51:19

Willy Vanhaelen

The immediate window is a very usefull tool. When I did what you suggested I get the expected result. But unfortunatly it doesn't work all the time. When I do it with both my slightly shorter alternatives, I get a runtime (13) error when i press enter on ?strEval. But when I use this code in a macro, it works perfectly.

When I use Rick's code line I get for example:
But that doesn't explain how this result is generated by:
Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address)
The @ has no special meaning. You can replace it with any character or word, even numbers.

2017-04-03 02:47:25

Alan Elston

Your welcome. I have spent many frustrating hours pulling apart Rick’s one liners in order to understand them, especially the Evaluate(“ “) ones
The best tip is to look at the final string used in the immediate window.
Like do this:
strEval=Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address)
Debug.Print strEval
‘ Hit Ctrl + g from the VB editor Window to bring up the Immediate window
The string you see in the Immediate window is then exactly as you would type physically into a cell , ( sometimes the = is missing as you can leave that out when doing a formula in Evaluate(“ “) , so you would then need to include a = when typing into the cell )

2017-04-02 06:44:13

Willy Vanhaelen

Interesting stuff. Thanks.

2017-04-02 02:42:23

Alan Elston

@ Willy
Here is some stuff that might help you with understanding of theose sort of code lines..
You also need to get a good feel for using quotes in Evaluate when trying to go through some of Rick's Evaluate one Liners

2017-04-01 10:19:52

Willy Vanhaelen

@Rick Rothstein
Here is even a shorter one-liner that works equally well:

Sub ZIPShorter()
Selection = Evaluate("IF(Row(),LEFT(" & Selection.Address & ",5))")
End Sub

2016-07-06 12:08:34

Willy Vanhaelen

@Rick Rothstein

I like one-liners but I don't understand quite well how this one works. Anyhow I have been experimenting and came up with a slightly shorter version which seems to work equally well:

Selection = Evaluate(Replace("IF(row(@),LEFT(@,5))", "@", Selection.Address))

I guess the @ acts like some place holder for the Selection.Address array but the arguments you use don't match the usual syntax of Replace at all. Can you elucidate?


2016-07-02 05:05:27

Rick Rothstein

You can write the macro version without using a loop...

Sub ZIPShorter()
Selection = Evaluate(Replace("IF(@="""","""",LEFT(@,5))", "@", Selection.Address))
End Sub

2016-01-27 21:43:32


I see that part of the procedure in the first solution presented involves converting the =LEFT(*,*) formulae in column H to values without moving them around.

A simpler way of accomplishing this formulae-to-values-in-place step I often use is, after selecting the formulae:

(1) Set the mouse somewhere on the border of the selection (the mouse pointer changes from a bold cross to a bold upper left-pointing arrow with four additional smaller up/down/left/right arrows).

(2) Right-click and then drag the selection to some other nearby area, then back again to its original area, all while keeping the right-click active. You can go as far away as you like before dragging back "home", but even just one column away will do (or one row away if your selection happens to not be an entire column as is the case in the given example).

(3) Now release the right-click and a multi-option contextual menu appears, one of whose options is "Copy Here as Values Only". Click this option (or use V as the hot key) and you're done.

You must do the drag-away-and-back step to make this contextual menu appear; it won't work by simply right-clicking on the border alone.

You can also use this procedure to simultaneously move the selection to a different area and change all formulae within it to values. You need not drag the selection back to its original location — although in this case the original formulae will persist where they were first created and may no longer be of any value, especially if the move is into cells used by the formulae. The user must decide for themselves what to do afterward with the formulae cells. If the move was indeed into input cells then the formulae will likely no longer have correct results and should be deleted.

This procedure works on my copy of Excel 2007; I can't confirm for other/newer versions and will leave this for others to verify.

2016-01-26 13:47:24

Paul Hunt

Great tip on how to remove +4 extension from zip code column. Have a list of over 200,000 lines that had to be formatted correctly, this did it. Much better help than Windows MS Excel help.

This Site

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

Newest Tips

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.