Separate detailed time stamp in google sheets

Question

I have a time indicator looking like this: 2002-01-01T05:00:31.217Z

I want to separate this in day ("yyyy-mm-dd") and time("hh:mm:ss") in tzo different rows.

Command =text(field, "yyyy-mm-dd") does not work, it still displays 2002-01-01T05:00:31.217Z

The application is google sheets. How can I separate it correctly?

Answer 1

use:

=INDEX(SPLIT(A2:A, "TZ", 1))

if you want to reformat it:

=INDEX(TEXT(SPLIT(A2:A, "TZ", 1), {"dd/mm/yyyy", "hh:mm:ss"}))