Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    excel formula for auto generating record numbers

    I am trying to auto generate numbers for a group of records that follows a sequence, ie: record one is ATT0001, record two is ATT0002 and so on but cant get the formula to add 1 to the string ATT000X... any assistance would be appreciated :-)
    Last edited by trish12; 2012-01-27 at 13:23. Reason: unclear question

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Do the records always have the format: XXnn (i.e., two letters followed by two numbers)?

    Off the cuff...if I understand you correctly:

    If the cell where AW12 (or nothing) is located is A1 and your new cell is B1, in B1 try:

    =IF(isblank(A1),"",LEFT(A1,2)&1+RIGHT(A1,2))

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Trish

    There are lots of ways do to this:
    If you have two cells with ATT0001 AND ATT002 in them, then select both cells and, putting the mouse pointer on the bottom -right corner of the selected pair of cells, just drag down (or across) and you will automatically get the sequence generated for you.

    Method 2: Use a Custom format:
    In an empty cell, enter the number 1
    Now use the Format Cells command to Format this cell as a Custom format, using "ATT"00000 as the custom format (include the double-quote marks in the custom format).
    Now copy this cell down a few rows.
    In the cell underneath the first cell containing the 1, simply use a formula to add 1 to the value.

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    trish12 (2012-01-27)

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    If the A1 cell could be any length but leads with characters and ends with numbers, try this in B1:

    (this is an array formula: CTRL+Shift+Enter)

    =IF(ISBLANK(A1),"",(LEFT(A1,MATCH(TRUE,ISNUMBER(1* MID(A1,ROW($1:$4),1)),0)-1)&1+RIGHT(A1,LEN(A1)-(MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$4),1)),0)-1))))

  6. The Following User Says Thank You to kweaver For This Useful Post:

    trish12 (2012-01-27)

  7. #5
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks for the assistance, I dont think I am explaining well...

    All records need a number of XXnnnn so I am trying to do a formula that will auto gen a sequential number in that format in column C whenever you add a record -?

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    You can use a simple formula to find the numeric value of previous record XXnnnn:
    Suppose the previous record was in row 8, so that cell [C8] has, for example, the value "AT0015"
    Then, in cell [C9] enter the formula:
    =LEFT(C8,2) & RIGHT("0000" & (RIGHT(C8,4)+1),4)
    ..then copy this formula down as appropriate

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    trish12 (2012-01-27)

  10. #7
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks so much! I added an IF statement to leave that autogen number blank if there was no record in the new row and it works great!

  11. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    Well done. I knew you could do it!
    I like it when the work is shared!

    zeddy

Posting Permissions

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