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

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

Regex

Parse

Accepted answers

Luke_C

Hi @c_raviram

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

New Workflow2.yxmd

All comments

Luke_C

Hi @c_raviram

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

New Workflow2.yxmd

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

20211109 - ExtractMonthYear.yxmd

HomesickSurfer

Hi @c_raviram

Here's my non-Regex solution.

Workflow attached.

New Workflow4.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703