I have been searching the forums and found many questions about parsing, then date/time conversions, but I am having a hard time putting the pieces together.
I have a field "filename" that contains values such as:
FileName |
Verbal Notification List-December 01 2018 01-30-30 |
Verbal Notification List-December 04 2018 01-30-33 |
Verbal Notification List-December 04 2018 930am |
Verbal Notification List-December 05 2018 01-30-42 |
Verbal Notification List-December 05 2018 10 am |
Verbal Notification List-December 05 2018 10-28-21 |
Verbal Notification List-December 06 2018 01-30-21 |
Verbal Notification List-December 07 2018 01-30-35 |
Verbal Notification List-December 07 2018 1030am |
Verbal Notification List-December 07 2018 17-38-09 |
The only part I care about is the Month DD YYYY (I am not concerned with the timestamp after, which I know is not in a consistent format). Ideally, I would like to convert these felds to:
FileName | New Field |
Verbal Notification List-December 01 2018 01-30-30 | 12/1/2018 |
Verbal Notification List-December 04 2018 01-30-33 | 12/4/2018 |
Verbal Notification List-December 04 2018 930am | 12/4/2018 |
What would be the best way to solve this in Alteryx? I have been trying some combination of parsing, then timedate conversion, but am struggling thus far.
Thank you.
Solved! Go to Solution.
Hi @zbowden2010, try this formula. REGEX_Replace([FileName], ".+?(\w+\s\d+\s\d{4}).+", "$1")
Also, wrap it in a datetimeparse formula if you want to convert the field to a date. DateTimeParse(REGEX_Replace([Field1], ".+?(\w+\s\d+\s\d{4}).+", "$1"), "%B %d %Y"), then set the field format as "Date".
Thank you so much, that worked perfectly!
Can you mark an answer as correct please ? it helps everyone :)
Sorry about that--these notifications were going to my SPAM box. I just marked the solution.