Split and Choose

Question

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 Answer

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 Answer

Here's the example with your test file