Zip Codes Missing the Leading Zero? February 2, 2010Posted by computertrainer in Excel Tips.
Tags: Concatenate, Excel, leading zero, LEN, zip code
In the New England states our Zip Codes begin with a zero. By default, Excel will remove the leading zero when entering data in a cell. For example, if you type 03082 and Enter, the cell will display 3082.
You can prevent this by formatting the column BEFORE entering data:
- Select the column that will hold the Zip Codes.
- [RIGHT] mouse click on the column letter and select Format Cells.
- On the Format Cells dialog box, select the Number tab and then select Text.
- A Zip Code like 03082 will now appear as 03082!
But what do you do if the data is already in the worksheet and the zeros are missing for the New England states? Below I will show you how to use the Concatenate function to add the leading zero to the cell. Yes, you can format the column with the Special Zip Code formatting but when you use the data in a Word Mail Merge, the leading zero may not display in Word.
So, let’s use the Concatenate function to actually put the zero in the cell:
- In an empty cell type ‘0 (apostrophe zero) (cell G1 in example above)
- In an empty column, in the first row below the Field Names, (G2 above) enter the Concatenate Function.
You can use the fx icon and use the Function Argument box or you can type the function into the cell.
The Text1 will be the cell with the ‘0 and the Text2 will be the first cell with a zip code missing the leading zero.
- Note that in the example above G1 has been made Absolute so when the function is copied down to the other rows, the cell G1 will “absolutely” be referred to.
You can type the $ in or with the mouse in function next to the G1 you can press the F4 key and both $ will be added – giving $G$1.
- Press Enter. G2 will have a function in it but the value is showing 03082.
- AutoFill the function down to the other rows with missing leading zeros.
- Select the cells in column G with the functions, then [RIGHT] mouse click and select Copy.
- [RIGHT] mouse click on F2 (in this example) and select Paste Special and then select Values from the dialog box.
You can delete the column where you created the Concatenate function.
How can you make this process easier if the leading zeros are all through the zip column? I’ll show you a function that will tell you the number of characters in a cell, then you can sort by that column. All the cells missing the leading zero will have four (4) characters while the others will have five (5).
- Click in an empty column in the top cell below that title row (G2 in the sample above).
- Enter the function LEN by typing =LEN( then click in the zip code column for the row you are on. Finish by typing ) and pressing Enter.
- AutoFill the function down to the last Zip Code.
- Sort A-Z (ascending) based on this column with the numbers. The cells missing the leading zero will have a 4 and will be above those with a 5-digit zip code.
- Once the Zip Code column has been sorted, you can delete the column with the LEN function and follow the Concatenate instructions above.
As always, if you have questions, please get in touch with me at Gretchen@UhasCT.com.