Extracting the date from the title

[suds144]

Hi Everyone,

I have been trying to use the "test to columns" tool to extract the date from the title but I'm unable to extract the date the way I want.

Output

Input

Thank you :)

[mbarone]

If I understand correctly you want to extract the date/time from each record?  If so, will the records always end with the date/time?  Will the format always be "# Month Year, start time - end time" ?

[suds144]

Yes, it will always be "# Month Year, start time - end time"

In some instances there is no "# Month Year, start time - end time" so that will be empty but that's ok. I just would like to extract the title from the date (the title is the most important in this case).

Thank you

[mbarone]

Probably lots of ways to do that.  I would start by creating a reference table (Text Input Tool) with 12 records and 2 columns.  1st column is all the months, and the 2nd column is the months again, but with a pipe to the left for replacement:
Month         Replacement
January      |January

February    |February

and so on

Then use a Find/Replace tool so your "event" replaces the month names with the month names preceded by a pipe ( | character). Then use a RegEx tool in "replace" mode with the expression \s\d+\s\|.* 

This will say "look for a space, followed by some digits, followed by another space, followed by a pipe" and remove everything from that pattern onward.

Give a try building it out - it'll be a good exercise using those two very powerful tools (Find/Replace & RegEx).

Let us know if you run into any issues.

[suds144]

This is great, thank you! I still have some numbers left as sometimes information is present as follows "Time, Day "

How can I remove the numbers at the end of the title?

[suds144]

I realised the format for "# Month Year, start time - end time" isn't always the same. So it also won't filter out if there is a bracket in front of the title

For example: Unleashing you creativity (Wednesday 6th October - 10:00 am -11:00 am) doesn't get filtered or cleaned

[mbarone]

Would have to first determine the various formats that the date can come in (looks like it is not as previously though - consistently).  

Then would have to play around with RegEx.  If you google RegEx, and RegEx coach, there is a lot of helpful items to be found.  I myself am no expert in it.

Or, you can create many reference tables like the month one - maybe one for each format and use the same methodology.

[suds144]

Thank you! I actually tried creating two other reference tables but it did not work. There is something I'm not doing right. I am not familiar with the Regex tool either. I have tried googling too.

Any simpler solutions? I though could split the date from title of the event? 

[EN6924]

Please find the solution -

[mbarone]

Unless you have a consistent format for the incoming data (as in, the same format all the time), you're going to have a difficult time using a simple text to columns solution.  I too knew nothing about RegEx when I started.  Spent a lot of time learning it (and still far from even "quite good" at it), but it's totally been worth it because I use it all the time now.