Parsing String with RegEx

[knnwndlm]

Hello,

Could somebody please help me parse the following string into multiple columns using RegEx?

I have a column as follows:
CA RATE (1/1/13-12/31/16)
NZ RATE (1/1/16-3/31/17)
NZ CURRENT RATE (1/1/20)
CA CURRENT RATE (4/1/21)

What I want to get is as following three columns:
CA RATE                      1/1/2013     12/31/2016
NZ RATE                      1/1/2016      3/31/2017
NZ CURRENT RATE   1/1/2020     12/31/2022
CA CURRENT RATE   4/1/2021     12/31/2022

Thank you!

Konn

[Luke_C]

Hi @knnwndlm 

Here's one way (no need to use regex).

1. Split data on parenthesis
2. Split data on hyphen (date)
3. Use datetime formulas to update the dates to the desired format, or fill in empty date with 12/31 of current year. 

[DataNath · Accepted solution]

You'll just need to chuck a formula or DateTime tool on the end to format the dates how you'd like! Workbook attached.

[messi007]

@knnwndlm,

Another way with RegEx and dateTime :

Attached the workflow,

Regards,

[alisonpitt]

This will get you the 3 columns without having to add the 12/31/2022 ahead of time, but you'd then need to add in a formula and datetime tool to clean up the nulls and the formats:

[A-Z\s]{1,}(?=\ \()|\d*\/\d*\/\d*

Spoiler

[binuacs]

@knnwndlm Another way of doing this

[knnwndlm]

Thank you All for your quick reply.  This is incredibly - the support and speed of the replies.  Have a wonderful day!