Custom Table Row ID Number

[Herbert Hessong]

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.

[#2]

Herbert,

what are the lowest and the highest values?

[#3]

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)

[#4]

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

[#5]

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.

[#6]

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

[#7]

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

[#8]

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?

[#9 · Accepted solution]

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

[#10]

Thank you! This formula does exactly what I need.