+ Reply to Thread
Results 1 to 4 of 4

add leading zeros to create a fixed length

  1. #1
    Registered User
    Join Date
    03-22-2009
    Location
    austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    add leading zeros to create a fixed length

    I need to create a IF statement where it will count the character length and if not 6 then add leading zeros to field to make a fixed 6 character length .. so if field is 489 then the if statment would make it 000489

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: add leading zeros to create a fixed length

    Hi,

    are your values numbers or text? If they are numbers, you could apply a custom format of

    000000

    which would format any number with as many leading zeros as required to make a 6 digit number. This would still be a number that you can use for calculations.

    The following formula will take numbers or text and add leading zeros. The result is text.

    =REPT("0",6-LEN(A1))&A1

    Exchange A1 for the cell where your original value is.

    cheers

  3. #3
    Registered User
    Join Date
    12-06-2003
    Posts
    21

    Re: add leading zeros to create a fixed length

    Quote Originally Posted by knita View Post
    I need to create a IF statement where it will count the character length and if not 6 then add leading zeros to field to make a fixed 6 character length .. so if field is 489 then the if statment would make it 000489
    Don't need to use IF statement. Format the cell.

    Right click on the cell, hit FORMAT CELL. Choose NUMBER, then CUSTOM.

    Under "Type" enter 00000# (five leading zeroes and a number sign)

    That's it. The cell will always maintain the fixed character length.

    Is that what you needed?

  4. #4
    Registered User
    Join Date
    03-22-2009
    Location
    austin, texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: add leading zeros to create a fixed length

    Yes - this solved my problem I can use either solution
    THANKS !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1