5 Replies Latest reply: May 30, 2012 10:08 AM by simfizx
eld3000 Level 1 (0 points)

Trying to upload a mailing list that requires XLS format which is no problem, my issue is that I have a list of businesses in NJ where the Zip starts with a Zero, and Numbers seems to automatically remove the Zero which causes the upload of my list to fail, but it looks like the wrong zip code.

 

Please help.

 

Thanks


iMac, Mac OS X (10.7.4)
  • eld3000 Level 1 (0 points)

    Maybe the question is how do a take a column with data in it and add a zero before what's already there.

  • Barry Level 7 (29,990 points)

    Numbers is 'recognizing' the string of characters as a 'number', rather than as 'text', and strips the leading zeros to display the 'number' in standard notation.

     

    Format the ZIP column as Text before entering the Zip codes, and the full string will be retained.

     

    For codes already entered, your second question applies.

     

    Zip codes come in two formats: nnnnn and nnnnn-nnnn

    The extended format will already be interpreted as text (with some exceptions), due to the hyphen in the middle of what would otherwise be a 'number,' so a formula needs to deal only with the original five digit variety.

     

    Here's an example. Column A has been formatted as text. Column B's format was left as Automatic, and the same keystrokes used to enter the codes as in the corresponding cells in Column A. Column C contains a formula to add zeros to the front of codes less than five characters long, and to convert all entries to Text.

    Picture 4.png

    Note the failures in lines 10 and 12. Numbers recognizes the string 12-3456 (with the leading zeroes ignored) as the month and year of the date shown, and interprets the entry as a Date and Time value.

     

    Formula:

     

    C2 (and filled down): =IF(LEN(B)<5,RIGHT("00000"&B,5),""&B)

     

    To use: 

    • Add a temporary column to the right of the column containing the Zip codes.
    • Enter the formula.
    • Copy the results
    • Click on the top cell in the original codes.
    • Go Edit > Paste Values
    • Delete the temporary column.

     

    Regards,

    Barry

  • eld3000 Level 1 (0 points)

    thank you but my GF helped me...added a column with 0's then another column using =concatenate(insert columns to be combined) 

     

    This makes a column w/ the numbers I want, then copy all that data and "paste styles"

  • Wayne Contello Level 6 (17,564 points)

    you can format the column with zip codes as "text" using the cells inspector.  Number attempts to treat numbers naturally which means you omit leading zeros.  The other way is to make a zip code formatter which will include the preceeding zeros

  • simfizx Level 1 (0 points)

    Or, if you're not going to do any math with the zip codes (they're not involved in any formulas), you can always start the zip code entry with a single quote (') character and any spreadsheet will treat it like text, leaving the leading zero.