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.
