+ Reply to Thread
Results 1 to 6 of 6

Question - Leading Zeros in Excel

  1. 03-21-2014, 08:08 PM #1
    prvimoon
    prvimoon is offline
    Registered User
    Join Date
    03-21-2014
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    69

    Question - Leading Zeros in Excel

    How do I stop Excel from removing the leading zeros by default? I usually export CVS files from a website I use and those files contain ZIP codes.

    I can't format the column to 'Text' since they display the leading zero but when you click on the cell and doesn't display the zero in front of it and with loads of CVS files, it's just too tedious to do. Also, some of those ZIP codes are from other countries that only have three or so numbers in it, making '302' appear '00302', which I don't want either. Nor do I want to put an asterisk or an apostrophe in front of the ZIP code, I'd rather have it work without adding anything extra. Can someone help? Thanks!
    Reply With QuoteRegister To Reply
  2. 03-21-2014, 08:12 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Question - Leading Zeros in Excel

    Hi and welcome to the forum

    Use custom formatting with 0's for teh amout of places you want to show...

    000 = 123
    0000 = 0123
    00000 = 00123
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-21-2014, 08:15 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Question - Leading Zeros in Excel

    Change the extension to txt, use the Text Import Wizard, and import the field as Text.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  4. 03-21-2014, 08:19 PM #4
    prvimoon
    prvimoon is offline
    Registered User
    Join Date
    03-21-2014
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    69

    Re: Question - Leading Zeros in Excel

    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    Use custom formatting with 0's for teh amout of places you want to show...

    000 = 123
    0000 = 0123
    00000 = 00123

    Thanks for the reply! As I mentioned in my original post, there's quite a few files I have to do, so formatting it as 'Text' or custom formatting each column in every file isn't really efficient. Also, some of the ZIP/Postal codes are 2, 3 or 4 digits, so a set format isn't going to work.
    Last edited by prvimoon; 03-21-2014 at 08:23 PM.
    Reply With QuoteRegister To Reply
  5. 03-21-2014, 08:22 PM #5
    prvimoon
    prvimoon is offline
    Registered User
    Join Date
    03-21-2014
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    69

    Re: Question - Leading Zeros in Excel

    Quote Originally Posted by shg View Post
    Change the extension to txt, use the Text Import Wizard, and import the field as Text.
    Isn't there any option in Excel that would just allow me to keep my zeros without having to do through the Wizard for all my files?
    Reply With QuoteRegister To Reply
  6. 03-21-2014, 08:34 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Question - Leading Zeros in Excel

    If you want to keep the leading zeros, you pretty much have 2 choises...
    custom format
    format as text

    Both can be done manually or through a small macro
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. A SQL question - Remove leading zeros
    By myobreportguru in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2013, 10:53 PM
  2. Excel Leading Zeros
    By [email protected] in forum Excel General
    Replies: 3
    Last Post: 01-17-2006, 03:10 PM
  3. support for leading zeros in excel
    By Vincenzo in forum Excel General
    Replies: 1
    Last Post: 12-30-2005, 12:20 PM
  4. leading zeros in excel
    By kiwi in forum Excel General
    Replies: 4
    Last Post: 05-17-2005, 02:06 PM
  5. save text field w/ leading zeros in .csv format & not lose zeros?
    By Ques in forum Excel General
    Replies: 1
    Last Post: 05-04-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1