Shorten ZIP Codes in Excel

By Allen Wyatt for Excel.Tips.Net

Shorten ZIP Codes in Excel

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)

3. Copy this formula into all the appropriate cells of column H.

4. Select the entire column H.
5. Press Ctrl+C. Excel copies the entire column to the Clipboard.
6. Display the Home tab of the ribbon.
7. 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.)




Figure 1. The Paste Special dialog box.

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

Article Continued here:


1,923 total views, 12 views today

(Visited 2,055 times, 3 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.