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

     

     

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