Forum Discussion

Herbert Hessong's avatar
Herbert Hessong
Copper Contributor
Apr 07, 2018

Custom Table Row ID Number

I am trying to create a ID number which goes up as I add rows to my table. The format I have the number in is 1AA001. When I try to go from 1AA999 to 1AB001, Excel's normal sequence continue does not work, and leaves me rolling back over to 1AA001. I was wondering if anyone could provide me with a set of functions or a format which will allow this to be done.

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 23, 2018

    Sorry, last bracket was missed when copy/pasted

    =IF(RIGHT(A1,3)+1<1000,
  LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000"),
  IF(CODE(MID(A1,3,1))<90,
    LEFT(A1,2)&CHAR(1+CODE(MID(A1,3,1)))&"001",
    IF(CODE(MID(A1,2,1))<90,
    LEFT(A1,1)&CHAR(1+CODE(MID(A1,2,1)))&"A001",
   TEXT(1+LEFT(A1,1),"0")&"AA001")
))

    and in attached file

     

     

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Apr 22, 2018

        Hi Herbert,

         

        ID could be calculated as

        =IF(RIGHT(A1,3)+1<1000,
   LEFT(A1,3) & TEXT(RIGHT(A1,3)+1,"000"),
   IF(CODE(MID(A1,3,1))<90,
      LEFT(A1,2)&CHAR(1+CODE(MID(A1,3,1)))&"001",
      IF(CODE(MID(A1,2,1))<90,
         LEFT(A1,1)&CHAR(1+CODE(MID(A1,2,1)))&"A001",
         TEXT(1+LEFT(A1,1),"0") & "AA001")
   )

        for previous cell in A1

         

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    Apr 08, 2018

    Hi Herbert,

     

    I am not expert but i do something for you. But i think it was not you expected.

     

    ="1A"&CHAR(LEFT(ROW()+65000,2))&RIGHT(ROW()+1000,3)

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Apr 09, 2018

      Logaraj, if go this way I guess ID:s like 1AB000 are to be excluded (i.e. for each thousand row)

      • Logaraj Sekar's avatar
        Logaraj Sekar
        Steel Contributor
        Apr 10, 2018

        Hi  and ,

         

        What about this one.

        ="1A"&CHAR(LEFT(ROW()+65000,2))&IF(RIGHT(A1,3)="999",RIGHT(ROW()+1001,3),RIGHT(ROW()+1000,3))

        But this is not for first ID number. i already told, i'm not expert. I tried it upto my level.

         

        Help if anyone find easy result.

Resources