Sorting a mixture of times & 'times as text' in the same column

Hi

I have bought together a number of worksheets containing lists of times into a new worksheet by cutting & pasting & now want to sort the complete aggregated list of times, smallest to largest, with anything that looks like a number, being sorted as a number.

All this works fine for the first 500 or so, but then a single set of times, ( some are text with apostrophe, some are just numbers ) do not sort & just sit at the end of the list in their own.

Example
the following are the last few items in the column that sort ok regardless of their format.
00:57:53
00:58:11
00:58:13
'66.15
'72:08

Next come a whole series of numbers, some formatted as text with apostrophe, others just numbers & they are not sorted 'into' the rest of the column but sit at the bottom of it.

'32.45
33.15
33.16
33.49
'34.00

I have tried 'format cells' & choosing the custom hh:mm as the required format for the whole column, but this does weird things to some of the numbers, so its no good. I presume I need one of the conversion formulas, but haven't been able to figure out which one I need.

I dont really care whether the column ( once its been reformatted or converted ) contains text or numbers, but just care that anything that looks like a number is sorted as if it were a number.
Any ideas how I can achieve this ?

Many Thanks

Hi shawfield

Try the following in F2 and copy down. Format as "[m]:ss"

Formula:

Please Login or Register  to view this content.

Hi shawfield

Select the D157:D199. Then find and replace: Find "." Replace: ":" (without the quotes)

In addition you have a few bogies in your data.
e.g. D47 has a leading space, and D188 has no colons it has 3 dots.

This might help if you are using 2003 as your profile states.
In G2 Drag/Fill Down

Formula:

Please Login or Register  to view this content.

In H2 Drag/Fill Down, Petes' suggestion.
=IF(G2>0.1,G2,INT(G2*60*60*24)/60/24+MOD(G2*60*60*24,1)*100/60/60/24)

If you are using 2007 or above as per your sample workbook you can do without the helper if you are into big formulae.

See this workbook

It assumes, as Pete does, that 02:44.42 is 2 hours 44 mins and 42 secs, and not 2 hours and 44.42 mins.