Formula for increment text value-CodePudding

I have the column with a combination of text and number - T-00001. I would like to have the formula to get T-00002, T-00003, and so on. I tried to use CONCATENATE("T-",RIGHT("T-00001",5) 1) but I get T-2, without 4 nulls. Is it possible to receive it in the correct format?

CodePudding user response：

if you want to drag it use:

=TEXT(ROW(A1), "T-00000")

if not, use:

=INDEX(IF(A2:A="",,TEXT(ROW(A1:A), "T-00000")))

if you plan empty rows use:

=INDEX(IF(A2:A="",,TEXT(COUNTIFS(A2:A, "<>", ROW(A2:A), "<="&ROW(A2:A)), "T-00000")))

for fixed range:

=INDEX(TEXT(SEQUENCE(20), "T-00000"))

to increment ony with each unique:

=INDEX(IFNA(TEXT(VLOOKUP(A2:A, {UNIQUE(FILTER(A2:A, A2:A<>"")), 
 SEQUENCE(COUNTUNIQUE(A2:A))}, 2, 0), "T-00000")))