So VALUE(3600*LEFT(A1,2)) is converting 00, the hours place, to seconds. Item two works on the next number 02, converting it to minutes. And so on. Now, notice in the full formula that each of the items in the list above are added + together. That gives you the seconds and the decimals, 122.3. If you are still having trouble understanding what this formula does, type it out slowly in Excel and pay attention to the formula hinting that pops up automatically. It tells you exactly what each part does.

1. If A1 is already formatted as text, leave it. Excel still uses it correctly in the formula in cell B1. Caution, converting back and forth between formats can permanently mess up your data. I strongly recommend working off a copy of your data, rather than the main file, because when you start playing with formats and text-to-number conversions (what the VALUE formula does), there is a good chance you accidentally and permanently change your data to something unusable.

VALUE(3600*LEFT(A1,2)) is converting 00, the hours place, to seconds. Item two works on the next number 02, converting it to minutes. And so on. Now, notice in the full formula that each of the items in the list above are added + together. That gives you the seconds and the decimals, 122.3. If you are still having trouble understanding what this formula does, type it out slowly in Excel and pay attention to the formula hinting that pops up automatically. It tells you exactly what each part does.

If you put all the items in the above list into their own separate cells, your output would be:

1. 0
2. 120
3. 2
4. 0.3

Put it together and what do you get? Bibbidi Bobbidi Boo! Add them up and it's your answer 0 + 120 + 2 + 0.3 = 122.3.²

1. If A1 is already formatted as text, leave it. Excel still uses it correctly in the formula in cell B1. Caution, converting back and forth between formats can permanently mess up your data. I strongly recommend working off a copy of your data, rather than the main file, because when you start playing with formats and text-to-number conversions (what the VALUE formula does), there is a good chance you accidentally and permanently change your data to something unusable.
2. At this point you might ask, why separate the numbers 02 and 300? the .300 is parts of a second and 02 is also seconds. They are the same thing. Well, your data in the future might have a peculiar separator in the future, like , or ;. Data I have separates it with ; and the trailing numbers are actually frames of a video within that time frame, so I have to do additional calculations on it to get decimal-seconds.

If you have 00:02:02.300 in a cell, it may appear exactly like this if it's formatted as text¹, or it may appear differently if it's formatted like something else.

The trick is easy to get seconds and miliseconds only, that is 122.300.

First, assume 00:02:02.300 is in cell A1. Highlight it and format it as a number. It will display something that looks like nonsense. Probably 0.00. Go to cell B1 and enter =A1*86400. Cell B1 will now contain 122.3. To get trailing zeros, format B1 as a number and adjust to your tastes.

1. If A1 is already formatted as text, leave it. Excel still uses it correctly in the formula in cell B1.

If you have 00:02:02.300 in a cell, it may appear exactly like this if it's formatted as text¹, or it may appear differently if it's formatted like something else.

The trick is easy to get seconds and miliseconds only, that is 122.300.

First, assume 00:02:02.300 is in cell A1. Highlight it and format it as a number. It will display something that looks like nonsense. Probably 0.00. Go to cell B1 and enter =A1*86400. Cell B1 will now contain 122.3. To get trailing zeros, format B1 as a number and adjust to your tastes.

To give Glorfindel's VALUE solution correctly, the formula would be

=VALUE(3600*LEFT(A1,2))+VALUE(60*(MID(A1,4,2))+VALUE(MID(A1,7,2))+VALUE(MID(A1,9,3))

I'll explain what this formula is doing. It is looking at the cell A1 as a text string, then converts parts of it to numbers, then adds them together appropriately to make them into total seconds. It has four parts:

1. VALUE(3600*LEFT(A1,2))
2. VALUE(60*(MID(A1,4,2))
3. VALUE(MID(A1,7,2))
4. VALUE(MID(A1,9,3))

Looking at the string 00:02:02.300, you can see it is basically four numbers separated by : and .. Each of those four numbers corresponds to the four parts above, from left to right.

So VALUE(3600*LEFT(A1,2)) is converting 00, the hours place, to seconds. Item two works on the next number 02, converting it to minutes. And so on. Now, notice in the full formula that each of the items in the list above are added + together. That gives you the seconds and the decimals, 122.3. If you are still having trouble understanding what this formula does, type it out slowly in Excel and pay attention to the formula hinting that pops up automatically. It tells you exactly what each part does.

To further keep your data organized and under your control, I recommend copying all the new cell data with your seconds and decimals how you like them, then paste as values only into new cells.

1. If A1 is already formatted as text, leave it. Excel still uses it correctly in the formula in cell B1. Caution, converting back and forth between formats can permanently mess up your data. I strongly recommend working off a copy of your data, rather than the main file, because when you start playing with formats and text-to-number conversions (what the VALUE formula does), there is a good chance you accidentally and permanently change your data to something unusable.