Forum Discussion

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

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...
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

     

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Apr 07, 2018

Herbert,

 

what are the lowest and the highest values?

 

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

The lowest value is 1AA001. The highest value is 9ZZ999.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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

     

    • Herbert Hessong's avatar
      Herbert Hessong
      Copper Contributor
      Apr 22, 2018

      I don't understand. When I try to run the function I end up with an error correction, resulting in 1002. Am I missing something in your response?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        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

         

         

Resources