Convert data from text format to time format in excel

Question

Is there a way to convert the following "text" data into a "time" data?

Here is a picture of the data:

I need to extract the numbers from the data for thousands of records at a time.

Does anyone have any ideas?

Answer 1

I solved it with the following steps:

1. Format the data as a table
2. Insert column
3. Fill the first two lines with the data in 00:00:00 format
4. Press Ctrl+E to flash fill the rest of the column

Answer 2

Since your data is uniform use this formula:

=LEFT(A1,2)+TIME(MID(A1,5,2),MID(A1,9,2),MID(A1,13,2))

Then format the cell:

[hh]:mm:ss