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
Solved! Go to Solution.
Hi @c_raviram
Here's an example using regex_replace & regex_match, although you can definitely accomplish the same results without regex.
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
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
User | Count |
---|---|
34 | |
28 | |
7 | |
7 | |
7 |