Adding lead zeros

[lgreco291]

Original question:
Hello...

I'm pasting data from an external source into Excel. The data is in hh:mm:ss format. However, when the source data has zero hours it only displays minutes and seconds preceded by a colon. For example, 5 cells of data in a column might look like this:

:29:28
:31:50
1:15:17
:05:47
1:19:36

I need to run simple calculations on these times, but the cells without an hours value paste into Excel as text format. What are some ways to add a zero before the colon in Excel?

Great Answer:
try
=(0&B2)+0 cell formated hh:mm:sss

My question: How do I prevent #VALUE when the cell is not missing data in front of the colon?

Example:
Works for :36:25
But, I get #VALUE for: 1:36:25

Thanks!

[shg]

Works fine for me either way.