Insert leading zero in a zip code

decook

New Member
Joined
Nov 18, 2003
Messages
4
I created a .csv file from another program that included as one of the columns a zip code. If the zip code starts with a zero, the zero was dropped.

Can anyone tell me how to tell Excel XP to look for a four digit number and add a zero to the front of it if it is four digits?

Something like, If I2 is 4 digits, add a zero to the beginning.

Thanks in advance,

Kent
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Format the column as Text so it will not drop the leading zero, then use the following formula:

=IF(LEN(I2)=4,"0" & I2,I2)
 
Upvote 0
I think you should be able to:

Select the cell your wanting to change, click format cells (either by the shortcut key, or right clicking then selecting format cells), click the Number tab, go to Custom in the left list. Now if the right side you will see "Type:" for every number you wish to be able to change put the # sign, and where you want the zero's put a 0. For instance:

A four digit number with the first always being zero: 0####.

Hope this helps.

-Zack
 
Upvote 0
Format the column for zip codes. Right click on the column, format cells, Number tab, special, Zip Code.
 
Upvote 0
you can also format custom and enter in 00000 (not # signs). I use it all the time for zip codes, ssn's and employee id's.
 
Upvote 0
Here's a formula I use

=string("0",5-len(Cell address)) & Cell Address

It will look at the value and if it is less then 5 characters in will insert a zero for each missing value.
 
Upvote 0
Heah that is cool but I get #NAME? Is that a UDF you have created?

Thanks . . . texasalynn
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top