Custom number format

[kctony]

I have a dilemma that I can't resolve and can find no relevant examples.
I have two tables with Class mnemonics (pharmacologic/therapeutic classifications) listed in the general format - 00:00.00.
when I download a report containing this information some are formatted as custom but with different custom formats and some are as general.
The last 2 digits may sometimes be intentionally blank which leaves some to show up as 00:00 while others might be 00:00:00 which adds to the confusion.
I need to get all these into a consistent format so I can do a VLOOKUP across the 2 tables and match data.
Also, if I try formatting to 'text' or 'number' I get very strange results and sometimes the format wants to make it time (complete with am or pm) aaaggghhh.
For example 28:08.92 sometimes shows up as 28:08.9. 28:08 may show up as 28:08 or 28:08:00. If it is formatted as general it is 0.019548, if it is number it is 0.06, there is a time format of 28:08.9 but that truncates the last digit. If I try to make a custom I get a message that says Microsoft cant use the number format I typed.
[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD="align: right"]40:20:00[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts?
Thanx

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
[TD="class: xl64, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

</tbody>

 

[Johnny Thunder]

Maybe try formatting the cells with a formula like this to keep the data consistent.

=TEXT(A1,"HH:MM:SS")

I have a dilemma that I can't resolve and can find no relevant examples.
I have two tables with Class mnemonics (pharmacologic/therapeutic classifications) listed in the general format - 00:00.00.
when I download a report containing this information some are formatted as custom but with different custom formats and some are as general.
The last 2 digits may sometimes be intentionally blank which leaves some to show up as 00:00 while others might be 00:00:00 which adds to the confusion.
I need to get all these into a consistent format so I can do a VLOOKUP across the 2 tables and match data.
Also, if I try formatting to 'text' or 'number' I get very strange results and sometimes the format wants to make it time (complete with am or pm) aaaggghhh.
For example 28:08.92 sometimes shows up as 28:08.9. 28:08 may show up as 28:08 or 28:08:00. If it is formatted as general it is 0.019548, if it is number it is 0.06, there is a time format of 28:08.9 but that truncates the last digit. If I try to make a custom I get a message that says Microsoft cant use the number format I typed.
[TABLE="width: 84"]
<tbody>[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD]92:92[/TD]
[/TR]
[TR]
[TD="align: right"]40:20:00[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.08[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
[TR]
[TD="align: right"]08:18.1[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts?
Thanx

<tbody>
[TD="class: xl64, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]

</tbody>

 

[kctony]

Maybe try formatting the cells with a formula like this to keep the data consistent.

=TEXT(A1,"HH:MM:SS")

Thanx, it was a good thought but I got a whole new set of strange numbers.
it is set up like CLASS:HEADING.SUBHEADING xx:xx.xx the fact that there is a colon and a period doesn't seem to jibe well with Excel. I might have a Class of 24 with a heading of 04 and a sub-heading or 44 = 24:04.44. If ther is no sub-heading it might be 24:04 (but sometimes it comes across as 25:04:00 whis is way wrong.
I've even tried Access but Access seems to want to convert that format to general... but only on some!
Weird.
thanx again