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?

PGSystemTester's avatar
PGSystemTester
Copper Contributor
Feb 19, 2025

You want each month to increment with the first three digits? The below formula would enable you to specify the starting date (month), the number of rows, etc. 

=LET(irows,200,iStart,285,dateStart,DATE(2025,2,1),
zSeq,SEQUENCE(irows,1,0,1),"TP"&BASE(zSeq+iStart,10,4)&" / " &TEXT(DATE(YEAR(dateStart),MONTH(dateStart)+zSeq,1),"MM / YY"))

 

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

    No the 2nd set is the month, the 1st set I want to change throughout the month when I issue a new number, the 2nd set would change when it rolls into March, 

     

    So it would be something like, 

     

    Feb:

    TP0285/02/25

    TP0286/02/25

    TP0287/02/25

    TP0288/02/25

    Mar:

    TP0289/03/25

    TP0290/03/25

    TP0291/03/25

    TP0292/03/25

    TP0293/03/25

    Apr:

    TP0294/04/25 and so on

     

    The times the 1st set of numbers change can vary depending on how many quotes I issue in a month. 

Resources