Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,
    I'm trying to solve an issue with formatting numbers on a spreadsheet that is creating serial numbers. I have attached a screen shot to begin with. The number of digits to format the cell to is determined by how many digits are typed in cell G2 (starting number). The forumulas work great until cell C21 when the number stays confined to 4 digits but the number in that cell needs to be 5 digits. None of that will make too much sense without looking at the attachement.
    I have to do it this way because I need to show leading zeros in the serial numbers. Is there a way to retain the leading zeros and stay variable with the number of digits for formatting.
    Thank you very much for any assistance that anyone can provide.
    - BOB-
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to be able to use 5 digit numbers, shouldn't you have entered 00001 in G2 instead of 0001, so that you get 00001, 01000 etc.?

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782314' date='29-Jun-2009 16:27']If you want to be able to use 5 digit numbers, shouldn't you have entered 00001 in G2 instead of 0001, so that you get 00001, 01000 etc.?[/quote]

    Thanks Hans,
    Wow...can't believe you read this already....thanks... You are quite correct...I should have entered 00001. Don't know what I was thinking....thanks and my apologies for wasting time on such a simple matter...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could also use these formulas in B21 and C21, respectively:

    =IF(D21="","",TEXT(C20+1,REPT("0",$G$5)))

    =IF(D21="","",TEXT(B21+D21-1,REPT("0",$G$5)))

Posting Permissions

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