Forum Discussion
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...
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
The lowest value is 1AA001. The highest value is 9ZZ999.
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
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?
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
Thank you! This formula does exactly what I need.