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 :)
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" ?
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
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.
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?
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
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.
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?
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.