Excel : Text to date conversion with milliseconds

Question

I am trying to convert a datetime string such as 2015-11-01-02.02.38.444000 to Datetime format in Excel. I have tried solutions provided in previous questions, but this particular format was not covered. I am looking to have both date and time in the same cell. Please assist.

Answer 1

It depends how consistent the strings are, but for this particular one you could try

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," ",3),".",":",1),".",":",1))

then use custom formatting to show it as a datetime value with milliseconds

dd/mm/yyyy hh:mm:ss.000

(you may have to change this for your locale).

BTW to make the TIMEVALUE formula in your comment work you would have to change the first two decimal points to colons.