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.

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

    Thanks that’s nearly there, the 1set of numbers 0285 in this case is a quote number, I may issue numerous quotes in February so that could go to 0297, then when it rolls into March the 02 would change to 03 and continue from the previous set of number, 0298 and so on, the 1 set of numbers keeps going up throughout the month, the 2nd set only change with the month changes and the last set when the year changes. 

Resources