Data dump time value challange

[MarkCBB]

Hi there,

I have a database that is downloaded, however some of the time values are not correct:

here is a sample:
0 7:37:26 PM
0 9:41:43 PM
0 9:50:13 PM
0 9:40:30 PM
0 9:42:45 PM
0 9:44:45 PM
0 9:45:15 PM
0 9:46:01 PM
0 9:47:01 PM
0 9:47:31 AM
0 9:49:31 PM
12:01:55 AM
04:51:55 PM
11:27:30 AM

Does anyone know why there are leading zeros?

Currently I am using the following formula to correct the entries

=TEXT(SUBSTITUTE(C17179,"0 ",""),"HH")

However I would prefer this to be a VBA solution, can anyone assist me?

the column in question is "C"

Thanks

 

[AlphaFrog]

Sub Strip_Leading_Zeros()
    
    Dim i As Integer
    
    Application.ScreenUpdating = False
    With Columns("C:C")
    
        For i = 1 To 12
            .Replace What:="0 " & i, Replacement:=i, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next i
        
        .NumberFormat = "HH"
        
    End With
    Application.ScreenUpdating = True
    
End Sub