Adding leading Zeros to values in a cell

  • I have several rows of numeric data that I need to add leading zeros to. When I try to add them using the FIND/REPLACE, the zeros disapprear. I am able to add them one by one but I need a faster way. I have thousands.

  • Re: Adding leading Zeros to values in a cell


    Welcome to the forum!


    One way would be to select your column with the data.
    Then Right Click and select "Format Cells"
    Select the Custom Option in the Categorie List
    In the "Type" text box enter "0###"
    Click OK


    That should do it for you. I enterd 3 "#" characters, but you can enter in as many as the length of your data is needed.

  • Re: Adding leading Zeros to values in a cell


    Quote from ccr01

    I have several rows of numeric data that I need to add leading zeros to. When I try to add them using the FIND/REPLACE, the zeros disapprear. I am able to add them one by one but I need a faster way. I have thousands.


    The suggestion of Bnix will do what you want. If you'd willing to store the numbers as text, then preceding by '0 will also work. But, you cannot do operations on the numbers no more (multiply etc.). They're text now.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Adding leading Zeros to values in a cell


    Thanks for the help. Unfortunately, this did not work for me. I believe the data was sent to me as text instead of numberic. I tried changing it to numbers but I guess it didn't work.



  • Re: Adding leading Zeros to values in a cell


    Well, you can do a formula as one way then.


    Add a column adjacent to the data you are trying to change
    In the first cell add =0&A1, where A1 is the first row of data you are trying to change.
    Either copy the formula, then highlight the entire column where you just placed the formula and select paste.
    OR
    Fill down as far as needed with the formula.

  • Re: Adding leading Zeros to values in a cell


    Hi,


    Try this formula:


    =TEXT(A1,"000")


    The above formula converts numbers to text to make sure there are three characters. Does this formula help you?


    Biz

  • Re: Adding leading Zeros to values in a cell


    FYI,


    with the VALUE function, you can convert text to values.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --