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 ReplacementJanuary |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 titleFor 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?
Please find the solution -
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.
Thank you! This partially worked but not all were split. There are still some events appearing with the date in the title?
Extracted the date in a separate column from your given input -
Thank you!. Would the formula have to be different if there is a "?" in the title or if there were no spaces between the date and the title? I'll review and detail and see.
Honestly been scouring the forum (and google) for tips. Must be my lack of knowledge and familiarity with the tool.
