Forum Discussion

Gtur1976's avatar
Gtur1976
Copper Contributor
Feb 18, 2025
Solved

Excel Formula Help - Text & Numbers

I would like to have a formula that will automatically change this sequence but can't figure it out, would greatly appreciate the solution as I cant find it searching either.   The sequence is TP02...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 20, 2025
    ="TPO"&ROW(A285)&" /  "&TEXT(MONTH(A5),"00")&" /  "&RIGHT(YEAR(A5),2)

    You are welcome. Does this formula return the intended result?

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 18, 2025

How about

="TPO"&ROW(A285)&" /  "&TEXT(IF(MOD(ROW(A2),12)=0,12,MOD(ROW(A2),12)),"00")&" /  "&QUOTIENT(ROW(A1),12)+25

 

Gtur1976's avatar
Gtur1976
Copper Contributor
Feb 20, 2025

Thanks that works, almost.

Just checking if it could be changed to read the date from the 1st column please, tried changing it myself but couldn't figure it out, as you can see the month doesn't change when I put in an different date.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 20, 2025
    ="TPO"&ROW(A285)&" /  "&TEXT(MONTH(A5),"00")&" /  "&RIGHT(YEAR(A5),2)

    You are welcome. Does this formula return the intended result?

    • Gtur1976's avatar
      Gtur1976
      Copper Contributor
      Feb 21, 2025

      Thanks.

       

      I changed some of the cells about and it now returns the correct result for my spreadsheet as the row numbers don't match the TP0 numbers.

      ="TP0"&ROW(A290)&"/"&TEXT(MONTH(A289),"00")&"/"&RIGHT(YEAR(A289),2)

      Thank you for your help.

Resources