Parsing data

[vchauhan011]

Hello,

Below is my data set.

10/25/2016 11:31:40 AM - Chandler M Bing_x000D_
10/25/2016 9:13:56 AM - Joey Tribbiani_x000D_
10/24/2016 5:39:29 PM - Ross Geller_x000D_
10-Oct-16 2:00:22 PM - Chandler M Bing_x000D_

I want parse it in two columns. one with date and time and the other with names. however, I am not able to parse it in this manner due to the different date and time format. 

Can anyone suggest what should I do to parse it correctly? Thank you very much in advance! 

[danilang · Accepted solution]

Hi @vchauhan011 

Here's one way you can do it

The regex tool uses this statement to break the input into a DateTime field and a name field, while getting rid of the annoying linefeed character at the end (_x000D_)

(.*?[AM|PM]) - (.*?)_x000D_

The formula tool uses this to parse the DateTime

if contains([DateTime],"/") then
	datetimeparse([DateTime],"%m/%d/%Y %I:%M:%S %p")
else
	datetimeparse([DateTime],"%d-%b-%y %I:%M:%S %p")
endif

This handles the two formats that you had in your input.  If there are more, you can add more clauses.

How come no love for Phoebe?

Dan 

[vchauhan011]

@danilang That is Brilliant! Thank you for this solution! Appreciate your help. 

even I am baffled that I put Ross's name and forgot Phoebe.

Vish