Number stored as text

Hello - I'm sorry for beating a dead horse, but I've tried many ways to convert text to numbers, but none seem to work.

When I import my lab results, excel won't recognize the results as numerical values unless I manual change all the numbers. This is extremely frustrating when you have tons of results.

I've Tried:
VALUE function to convert numbers stored as text to usable values
convert numbers stored as text to values by multiplying the cells by 1.

I've attached a sample workbook of the issue.

I'd greatly appreciate any help and please note I'm a novice.

Thanks

Excel Problem 1.xlsx

It seems that removing first 4 characters 000: does the trick.
You can use this formula

=--REPLACE(A1,1,4,"")

format cell Custom, mm:ss.00

Hi and welcome to the forum

Do a sheet-wide Find/Replace....

Find 000:
Replace 00:

Ford beat me to it.

Use the SUM function for the totals if you need to. Format the totals in column N as [h]:mm:ss.0

Ford's solution works beautifully.

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Happy to help and thanks for the feedback

unfortunately it's creating another problem. Excel is reading the numbers now, but when using average function, it is not correctly averaging times. It's confusing the times again due to the format.

The prior solution was converting the numbers into seconds so that the data could be utilized (scientific experiment).

Any suggestions for a work around? I was thinking of creating a macro that would replace all the time numbers into seconds, but that seems pretty tedious anyone think of any easier solution?

fixed --- converted using [s] seems to work.