Add +1 to a year or digit in a string

Question

I'm currently building a workflow that needs to roll forward the year in a string. It's all in the same column and would need to just increase by one year. See some examples below:

Original: Contract calculation for FY24-25

Need to update to: Contract calculation for FY25-26

Original: FY23-24 Employment Agreements

Need to update to: FY24-25 Employment Agreements

Original: TB detail as of 6/30/2024 in excel

Need to update to: TB detail as of 6/30/2025 in excel

Original: GL detail 7/1/2023 - 6/30/2024

Need to update to: GL detail from 7/1/2024 - 6/30/2025

I've tried Replace and REGEX_Replace functions but still running into issues. Any help is appreciated!

Yoshiro_Fujimori · Accepted Answer

I tried to solve this as a challenge.

Please note that the workflow depends on the input data pattern, and you may need to modify it as you find issues.

Input Data

Field1Contract calculation for FY24-25FY23-24 Employment AgreementsTB detail as of 6/30/2024 in excelGL detail 7/1/2023 - 6/30/2024

Workflow

Output Data

Field1Contract calculation for FY25-26FY24-25 Employment AgreementsTB detail as of 6/30/2025 in excelGL detail 7/1/2024 - 6/30/2025

flying008 · Accepted Answer

Hi, @tcrawf

FYI.

(\d{2}-\d{2})|(\d+/\d+/\d{4}\s?-?\s?(\d+/\d+/\d{4})?)

IIF(Length([Get]) =2, ToString(ToNumber([Get]) + 1), Left([Get], Length([Get]) -4) + ToString(ToNumber(Right([Get], 4)) + 1))

Trim(IIF(Contains([Txt], 'FY'), REGEX_Replace([Txt], 'FY\d{2}-\d{2}', 'FY' + Replace([Get], ' ','')), REGEX_Replace([Txt], '\d+/\d+/\d{4}\s?-?\s?(\d+/\d+/\d{4})?', [Get] + ' ')))