Regex expression to derive/calculate Month and Year from column 'months_yr'

[c_raviram]

Hi Experts, can you pls let me know a regex expression to derive month and year for below column "months_yr"

months_yr is month number and the years are differentiated with "_2".

Values with "_2" is for year 2022 and without "_2" is for year 2021

Sample example below:

I have column "months_yr" in my dataset and I am looking for a regex expression or formula to derive columns "Month" and "Year"

months_yr	Month	Year
1	01	2021
1_2	01	2022
10_2	10	2022
11	11	2021
11_2	11	2022
12	12	2021
12_2	12	2022

Regards, Ravi

[Luke_C · Accepted solution]

Hi @c_raviram 

Here's an example using regex_replace & regex_match, although you can definitely accomplish the same results without regex. 

[Jonathan-Sherman]

Hi @c_raviram,

I would use a regex formula to calculate the month and a standard if condition to calculate the year:

Month:

PadLeft(REGEX_Replace([months_yr], "^(\d+)_.*", "$1"),2,"0")

Year:

IF EndsWith([months_yr], "_2")
THEN "2022"
ELSE "2021"
ENDIF

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[HomesickSurfer]

Hi @c_raviram 

Here's my non-Regex solution.

Workflow attached.

[MarqueeCrew]

@c_raviram ,

I want to help you AVOID RegEx for this solution.  The ability to parse the data without RegEx is a reason not to use RegEx.  Here is a formula that creates a new variable that is easily parsed with a text to columns tool.  You can also alter the formula to create 2 variables in a formula if you so choose.

IF Contains([months_yr], "_") THEN padleft(left([months_yr],FindString([months_yr], "_")),2,"0")+"|202"+right([months_yr],1)
ELSE
padleft([months_yr],2,"0")+"|"+"2021"
ENDIF

Using the "FINDSTRING" function, you can get what you need out of the data.  Here is my browse output:

months_yr	Month	Year	MM|YYYY
1	01	2021	01|2021
1_2	01	2022	01|2022
10_2	10	2022	10|2022
11	11	2021	11|2021
11_2	11	2022	11|2022
12	12	2021	12|2021
12_2	12	2022	12|2022

Cheers,

Mark