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.

92:92

92:92

92:92

40:20:00

08:18.1

08:18.08

08:18.08

08:18.08

08:18.1

08:18.1

<colgroup><col></colgroup><tbody>
</tbody>

Any thoughts?
Thanx

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
</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.

92:92

92:92

92:92

40:20:00

08:18.1

08:18.08

08:18.08

08:18.08

08:18.1

08:18.1

<tbody>
</tbody>

Any thoughts?
Thanx

<tbody>
</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