Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Insert leading zero in a zip code

This is a discussion on Insert leading zero in a zip code within the Excel Questions forums, part of the Question Forums category; I created a .csv file from another program that included as one of the columns a zip code. If the ...

  1. #1
    New Member
    Join Date
    Nov 2003
    Posts
    4

    Default Insert leading zero in a zip code

    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

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,996

    Default Re: Insert leading zero in a zip code

    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)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,462

    Default Re: Insert leading zero in a zip code

    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

  4. #4
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default Re: Insert leading zero in a zip code

    Format the column for zip codes. Right click on the column, format cells, Number tab, special, Zip Code.

  5. #5
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,462

    Default Re: Insert leading zero in a zip code

    IML is right, it can also do zip code + four digit

  6. #6
    New Member
    Join Date
    Nov 2003
    Posts
    4

    Default Re: Insert leading zero in a zip code

    THANKS. Cheers to you.

  7. #7
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Insert leading zero in a zip code

    And in the alternative, see one of many discussions from the archives:

    http://www.mrexcel.com/board2/viewto...ip+code+format

    "Don't Ruin an Apology with an Excuse"...

  8. #8
    New Member
    Join Date
    Nov 2003
    Location
    Stratford, CT
    Posts
    17

    Default Re: Insert leading zero in a zip code

    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.

  9. #9
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: Insert leading zero in a zip code

    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.

  10. #10
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,308

    Default Re: Insert leading zero in a zip code

    Heah that is cool but I get #NAME? Is that a UDF you have created?

    Thanks . . . texasalynn

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com