Find and Replace Using Time Format

Hello Gurus,

The file in question is attached.

I'm trying to use a find and replace VBA to eliminate "0:0" and replace with "" ... but it doesn't seem to work for me.

You may notice that some cells do not have "0:0" and only have "0:35" etc... those should stay the same.

The macro also needs to loop through each cell.

Any help is GREATLY APPRECIATED!

Please Login or Register  to view this content.

Kind regards
Leo

Use autofilter like

Please Login or Register  to view this content.

Thanks for the responses guys, but none of these worked for me... did they work for you?

each cell with this format 0:01:39 would become 1:39...

and

each cell with this format 0:35 would stay the same...

This is what I was playing with:

Sub dude()

Columns("A").Replace What:="0:0", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Any more ideas?

You have 0:01:39 in A2, 0:00:00 (0:00 as in cell) in A13, 0:35:00 (0:35 as in cell) in A21, 0:1d 3:17 in A136.
Column is formatted as time "h:mm:ss".

How do you want them as a result?

Attached is how I get the raw data. I think this will help.

My goal is to convert each cell to hh:mm:ss

Obviously the extra text hr/min in the box will need to be removed before the conversion.

Maybe the hours and minutes need to be converted to seconds first to format to hh:mm:ss?

Any ideas?

See if this is how you wanted.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Kind regards
Leo

Perfect Jindon thank you!

i could be wrong, but my calculation has 27 hours and 17 minutes voor cell A136


Kind regards
Leo