Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: FORMATTING ZIP CODE COLUMN

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a column of zip codes. Some have the zip +4 extentions and some don't. What I want to do is make them all just with the zip code and no 4 digit extention.

    I also have a problem formatting the zip codes that begin with the number 0. It seems to cut the 0 off.

    Any help on how I can accomplish this.

    Thanks for any help

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 20:16, jbyrne wrote:
    I have a column of zip codes. Some have the zip +4 extentions and some don't. What I want to do is make them all just with the zip code and no 4 digit extention.

    I also have a problem formatting the zip codes that begin with the number 0. It seems to cut the 0 off.

    Any help on how I can accomplish this.

    Thanks for any help
    One, Format the ZipCodes as Text entries,
    Two, to make entry in A1 a five digit entry, in an adjacent column, use =LEFT(A1,5)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi,

    My zip codes are in column F and in column G I am typing your formula =LEFT(F1,5)

    I first format the zip codes as text. Then I enter in the formula in column G. I still seem to be missing the 0's in the zip codes that start with 0.

    When I open the file, (csv file) I have quotes around the zipcode. I don't know why this is happening either.


  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 20:37, jbyrne wrote:
    Hi Yogi,

    My zip codes are in column F and in column G I am typing your formula =LEFT(F1,5)

    I first format the zip codes as text. Then I enter in the formula in column G. I still seem to be missing the 0's in the zip codes that start with 0.

    When I open the file, (csv file) I have quotes around the zipcode. I don't know why this is happening either.
    Hi jbyrne:
    How about post some of the entries from column F and G ... and let us look at those.

    Regards!

    Yogi

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    COL E F
    "WI" "535115150"
    "FL" "338094123"
    "TN" "37087"
    "KS" "666111468"
    "KS" "66611"
    "KS" "666111466"
    "CA" "92692"
    "KS" "666042625"
    "MD" "217760744"
    "MD" "21776"
    "NJ" "080071049"
    "NJ" "08108"
    "NJ" "080432045"
    "NJ" "080574224"

    When I remove the quotes with the text to column method, the NJ zipcodes drop their 0's

    Thanks Yogi for your help

    Jim

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Once you get em down to five digits,
    just select the cells in question, right-click, Choose format cells, choose the number tab, choose special, choose zipcode...
    The zeros are still there, you just can't see 'em. With the above format, you get 5 numbers.. So if you type in 00005 you will see 00005...
    I think that will help you as well...
    Tom

    [ This Message was edited by: TsTom on 2002-05-03 21:15 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jim,

    When the master of formulae "Aladin" reads your post you will be in good shape. This guy
    is phenomenal, all business, no side jokes
    and a true gentleman.

    James

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    Thanks, that worked great.

    Thanks for your help!

    Jim

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 20:59, jbyrne wrote:
    COL E F
    "WI" "535115150"
    "FL" "338094123"
    "TN" "37087"
    "KS" "666111468"
    "KS" "66611"
    "KS" "666111466"
    "CA" "92692"
    "KS" "666042625"
    "MD" "217760744"
    "MD" "21776"
    "NJ" "080071049"
    "NJ" "08108"
    "NJ" "080432045"
    "NJ" "080574224"

    When I remove the quotes with the text to column method, the NJ zipcodes drop their 0's

    Thanks Yogi for your help

    Jim
    Hi Jim use the following formula in cell G1 and then replicate down:

    =LEFT(IF(OR(LEN(E1)=4,LEN(E1)=8),"0"&E1,E1),5)
    what this formula does is check if a ZipCode is either 4 (short), or 8 (long), then it attaches a zero to the ZipCode entry in the first digit location.

    Please post back if it works for you ... otherwise explain a little further and let us take it from there.

    Regards!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi,

    Thanks for your reply and I'm sorry for the delay in responding. I've been on vacation. I will try your formula tonight and let you know how I did.
    I know I had said the state was in col E and the zip was in F but I was mistaken.
    My state is in column F and my zip is in column G. Can you let me know what I need to revise in your formula?

    Thanks again
    Jim

Some videos you may like

User Tag List

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
  •