Excel not recognizing date in a csv imported date

[theokaygadsby]

Hello, thank you for reading.

I'm working with dates that are exported from Kibana like this:
[TABLE="width: 213"]
<tbody>[TR]
[TD]February 9th 2018, 18:00:00.000[/TD]
[/TR]
[TR]
[TD]I have tried using [/TD]
[/TR]
[TR]
[TD]=LEFT(A1,FIND(",",A1)-1))
and various combinations of the TEXT and DATEVALUE functions, and have not been able to get the value recognized as a date.

my goal is to extract the date into one column and the time into another and be able to sort on date, and format as different date formats as needed.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[BobUmlas]

This works:
=DATEVALUE(IF(ISERROR(1*MID(A1,FIND(" ",A1)+2,1)),LEFT(A1,3)&" "&MID(A1,FIND(" ",A1)+1,1)&", "&MID(A1,FIND(",",A1)-4,4),LEFT(A1,3)&" "&MID(A1,FIND(" ",A1)+1,2)&", "&MID(A1,FIND(",",A1)-4,4)))

But you might also try Flash Fill which works.
[TABLE="width: 202"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[BobUmlas]

Also, Power query works, too. Replace th with ",", nd with ",", and st with ","; split columns by comma, change date field to Date.

 

[theokaygadsby]

Thank You! that worked very well for me. Power Query is new to me, and I am new to Mr. Excel but I am glad it's out there. Thank You again!