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:

February 9th 2018, 18:00:00.000

I have tried using

=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.

<tbody>
</tbody><colgroup><col></colgroup>

 

[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.

<tbody>
</tbody><colgroup><col></colgroup>

 

[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!