Data Points

 View Only

  • 1.  Solving an annoying problem of missing leading zeros

    Posted 07-15-2019 16:23
    A simple tutorial for Data Geeks who get stymied by the USPS's desire to put leading zeros on US Postal Codes (my zipcode of 03766 gets conveniently converted to 3766 if I am not careful in Excel).  Our friends at Aggdata have a simple guide to solving this.

    Article is here:  Adding Zeroes Back to Zip Codes in Excel

    ------------------------------
    Tom Gilligan
    Pitney Bowes Software, Inc.
    White River Junction, VT, USA
    ------------------------------


  • 2.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-23-2019 13:33
    Is it possible to pad a number in Excel without it being a true zip code?  For instance, can I pad a number with zeros and it be as long as 20 digits?

    ------------------------------
    Carlton Hemphill
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 3.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-24-2019 04:21
    You can use the TEXT function:

    =TEXT(A1,"00000000000000000000")


    ------------------------------
    William Allen
    Data Engineer
    Pitney Bowes Software Ltd
    Henley-On-Thames
    ------------------------------

    Original Message


  • 4.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-24-2019 09:14
    I tried that out and the text format works, but if saved as a CSV then the zeroes disappear.  I use CSV for about everything.  Is there another file type to use that is just as good as a CSV (or readable) for programs like Arc, QGIS and Postgres?  Thanks!

    ------------------------------
    Carlton Hemphill
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 5.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-24-2019 09:31
    Oh right. I think the zeroes save fine to the CSV file - it's when you try to open the CSV in Excel again - Excel will then convert it back.

    If you open the saved CSV in notepad you'll see the zeroes. It should also import to your other systems fine!


    P.s. if you need to avoid the issue of opening in Excel and losing the zeroes - open a new blank workbook and go through the Data -> Import From text -> Then format the field as 'Text' during the import rather than the default 'General'.

    Hope that makes sense..! :)

    ------------------------------
    William Allen
    Data Engineer
    Pitney Bowes Software Ltd
    Henley-On-Thames
    ------------------------------

    Original Message


  • 6.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-24-2019 09:45
    Makes perfect sense!  Thanks a lot!

    ------------------------------
    Carlton Hemphill
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 7.  RE: Solving an annoying problem of missing leading zeros

    Employee
    Posted 07-30-2019 17:54

    If you are looking to do the same in MapInfo Pro, you can use the Format$() function which can format values in numerous ways.

    For this example you can use this expression to make sure your zip code is 5 digits with zeros at the beginning:

    Format$(ZIP, "00000")

    Be aware that the input value, the zip code, must be a numeric value. If it's a string, you can force it into a numeric value using the Val() function:

    Format$(Val(ZIP), "00000")

    So if you want to update your existing zip code column with zeros padded, you can use the Update Column statement or the Update Column dialog:
    Update ADDRESSES Set ZIP = Format$(Val(ZIP), "00000")



    ------------------------------
    Peter Horsbøll Møller
    Pitney Bowes
    ------------------------------

    Original Message


  • 8.  RE: Solving an annoying problem of missing leading zeros

    Employee
    Posted 07-31-2019 21:31
    If you'd like to control the number of digits shown in Excel you can choose cell format as custom with say 0000.000 and that means for the value 372.15 it would display as 0372.150 (0 indicates exactly one digit displayed, while '#' can indicate many if left of the decimal).
    This maintains the values as numbers that can be used in mathimatical functions (not desirable for zip codes - these should be text).​

    ------------------------------
    Jack Fifoot
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------

    Original Message


  • 9.  RE: Solving an annoying problem of missing leading zeros

    Posted 07-31-2019 05:05
    Edited by Warren Vick 07-31-2019 05:05
    While correcting a ZIP/postcode that has lost its leading zero(s) is relatively easy with format$(), I feel the best approach is to avoid the situation in the first place! ZIP/postcodes should never be handled as numbers as their numerical value usually have no significance. On import, software will naturally think that a ZIP/postcode field is numeric in nature, but there is usually a way of saying, nope... this is a 4/5 character string.

    Of course, Brits and our Canadian cousins don't have these problems as we threw some letters in our systems. :-) 

    #unnecessarycomplication



    ------------------------------
    Warren Vick
    Europa Technologies Ltd.
    London, U.K.
    ------------------------------

    Original Message


  • 10.  RE: Solving an annoying problem of missing leading zeros

    Employee
    Posted 07-31-2019 09:03
    Another approach commonly taken by MapInfo Pro users is to use the "RIGHT$()" function​ in a Table > Update Column procedure.

    Right$( )

    Syntax: Right$ (string_expr, num_expr)

    Action: Returns part or all of a string beginning at the right end of the string.


    With the ZIP column set to a data type of character, users can run this update to pad the front of the string with leading zeros and then
    select the right-most characters, starting from the end of the string - in this case we pad the front with 5 leading 0's (as there may be multiple missing zeros) and then grab the last 5 characters:

    See the MapInfo Pro Help section for more on String functions.


    ------------------------------
    [Dave] [Sepowski]
    [Support Analyst]
    [Pitney Bowes]
    [Troy] [NY]
    ------------------------------

    Original Message