Search code examples
google-sheetsuniquestring-formattingvlookupnumber-formatting

Formula for increment text value

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?

Solution

  • 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")))

    enter image description here

    if you plan empty rows use:

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

    enter image description here

    for fixed range:

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

    enter image description here

    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")))

    enter image description here