Leading zeros

I'm in a pickle so I come seeking the experts! :D

I have a range of data that contains cells that report time in hours mins and seconds. If the amount of time is in seconds only, it's reporting as :00 and causing me problems when I lookup to it...specificially it returns nothing. My formulas use iserror so prevent errors showing up.

What I need is a script or macro that searches the range (a1:z20) and finds all cells there the leftmost char is a colon and replaces it with 0:

Any help on this would be greatly appreciated. (I do 30 pages of this a day and need a faster way to go through them)

The way I understand it, you could do something like this:

VB Code:

If Left(Cells(row, column).value, 1) = ":" then
    Cells(row, column).value = "0" + Cells(row, column)

Stick that in a loop, and you should be good.

Are you storing the time as a string or as a number?

as a number is what I'm trying to do. Java, you said to stick it in a loop. Can I ask you to show me how to do a loop as I've never done one successfully?

Sure.

VB Code:

For i = startingNumber To endingNumber
    If Left(Cells(row, column).value, 1) = ":" then
        Cells(row, column).value = "0" + Cells(row, column)
    End if
Next i

OK, that is not going to work if you are storing values

VB Code:

For i = startingNumber To endingNumber
    If Left(Cells(row, column).value, 1) = ":" then
        Cells(row, column).value = "0" + Cells(row, column)
    End if
Next i

. The value of the cell doesn't contain a colon, that only in the format. To find cells that only have seconds you need to look for a cell value less than 0.00069444
(A day [24 Hrs] = 1.0 therefore 1 min = 0.00069444)

Are you sure? Because I tested it before I posted, and it worked fine for me.

It will work if you are storig a Text String, not if you are storing a datetime value.

Well, that's depressing...sorry about that.

Sorry for the delay. been on a wee bit of a tear. I'll give it a whack tomorrow morning at work and see the magic.

Thank you both for your help! This place is a godsend!