Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding leading zero's (2003)

    Hello all,
    I have inherited a large spreadsheet which I have to do some repairs on. One of the problems I have is a column that contains only numbers, but is formatted as Text. I need for this field to display every number as five digits. The problem is that previously this column was formatted as General and all of the leading zeros have dropped off, leaving only 1, 2 3, or 4 digits in the column in a lot of cases.
    For example 123 should be displayed as 00123, or 1 should be displayed as 00001.
    This workbook contains 10000 records so changing them individually is out of the question. Is there a way I can change all numbers of less than 5 digits to display leading zeros to make up my five digit number?
    By the way I have now formatted that column as Text if that helps.
    Thanks heaps.

    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding leading zero's (2003)

    1) Select a blank cell (formatted as General)
    2) Copy it to the clipboard (Edit | Copy)
    3) Select the cells that need 5 digits.
    4) Select Edit | Paste Special...
    5) Click on Add, then OK.
    6) Select Format | Cells...
    7) Activate the Number tab.
    8) Select the Custom category.
    9) Enter 00000 in the Type box.
    10) Click OK.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding leading zero's (2003)

    You could use Custom Number Formatting!

    Select the column and press CTRL + 1 (to open the Format Cells Dialog)
    Select the numbers tab and choose Custom at the bottom of the list
    In the General text box, type - 00000
    Chose OK

    This will format the numbers to 5 digits with leading zeros where needed
    Regards,
    Rudi

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading zero's (2003)

    Rudi/Hans,
    Thank you both for your help.
    Both solutions worked perfectly. Much appreciated.

    Bill

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading zero's (2003)

    Hans and Rudi have told you how to display the number values with leading zeros. However, your post indicated that you really wanted to change the numeric values to text values with leading zeros. Just changing the format to text will not convert a numeric value that is already in the cell to a text value. If you really want to change the values to text (and I think that is what should be done if these are not values that will be used in calculations), then the procedure below will do that. The procedure below assumes that the values are in column A, if that is not the case, modify it as required.

    1- Select an empty column and enter this formula in cell 1 of that column:

    <code>
    =TEXT(A1,"00000")
    </code>

    2- Fill that formula down the column as far as the numbers go in column A. You should now have text with leading zeros displayed.

    3- Select column A and make sure the format of all the cells is TEXT.

    4- Select the column with the formula above and do an Edit/Copy.

    5- Select cell A1 and then do Edit/Paste Special.

    6- In the Paste section of the Paste Special dialog box, put a tick mark next to Values. Click on OK.

    7- You can now delete the column with the formulas and you should have text values in column A. Any new values you enter in column A should be entered as text values.
    Legare Coleman

Posting Permissions

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