Split and Choose

[LisaBrown]

I have a column that I need to split and pick particular parts of it. The format is as below:

Date

19-NOV-17/19-NOV-17/01-DEC-17/01-DEC-17
20-NOV-17/21-NOV-17
18-NOV-17/22-NOV-17
18-NOV-17/22-NOV-17

04-DEC-17
04-DEC-17/07-DEC-17

What I know is that there are situations where the date value can be null and as the data above suggests number of dates in a single column are variable. It can probably go upto 5 at max. but there might me a rare case of 6. 

What I'll need is the 1st and last date here. I cant figure out a way to split it and pick only 1st and last ones available.

Any suggestions on how to go about it?

[LordNeilLord · Accepted solution]

Hi Lisa,

When there multiple dates you can use the regex tool to parse the first and last date:

(^\d{2}-\w{3}-\d{2}).*(\d{2}-\w{3}-\d{2}$)

This doesn't work when there is just one date but you can add a formula tool to fill out any gaps

[LordNeilLord · Accepted solution]

Here's the example with your test file

[LisaBrown]

That was great! Can you link me to a tutorial on regex tool as well?

[LordNeilLord]

Sure thing:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689