How can I add a zero before these fields in Excel 2010?

fjcpa June 4, 2013

I have a series of numbers in many rows, each with uneven length because the numbers 1 through 9 do not have a number zero preceding the numbers. I would like to add a zero before each incidence of the numbers 1 to 9 in the series of numbers.

I am using Microsoft Excel 2010. Any suggestions?

  1. Scott Kupferman
    June 7, 2013 at 3:11 pm

    Suppose in A1 you have the value 23
    In B2, you want the value 0023
    In B2, type =text(A1,"0000")
    B2 should now display 0023.

    Padded zeros are still text though and aren't easily added.

  2. Paul Pruitt
    June 4, 2013 at 2:51 pm

    You can also turn the number into text by adding an apostrophe before them that will not display but lets Excel know they are text. The downside however is I think you can't use the numbers in formulas...

  3. Oron Joffe
    June 4, 2013 at 2:12 pm

    Adding to what Bruce and Alan have already said, what you want is called "leading zeros". Look it up in the help and I'm sure you'll find a lot of neat formatting information there!"

  4. bud i.
    June 4, 2013 at 1:39 pm

    Use the format "00.000" (without quotes), if the values contain decimal fractions. This will align the right-hand margin with three places after the decimal point.

  5. bud i.
    June 4, 2013 at 1:31 pm

    If the values contain fractions, the format would be "00.000" if you wanted to display three places after the decimal and align the right hand margin.

  6. Alan Wade
    June 4, 2013 at 6:52 am

    Just to add to Bruce's answer. Highlight all the cells that you want this to apply to and right click then select Format Cells, that will take you to the formatting window.

  7. Bruce Epper
    June 4, 2013 at 5:54 am

    Change the cell (or column or row) formatting to Custom and the format to "00" (without the quotes) so all single digit numbers will appear with a leading zero. You can extend this to any number of leading zeros you require.

Ads by Google