Adding space inside a column-CodePudding

I have this issue with putting space in between time and the time abbreviation (1:32AM), I have tried using the =LEFT and =RIGHT functions but since the time is inconsistant (1:32AM, 11:32AM) and there being 15000 rows of data, I wasn't able to separate the time.

Haven't tried anything in VBA, open to any suggestions.

Thanks

CodePudding user response：

Correct Time VBA

Option Explicit

Sub CorrectTime()
    
    Dim sStrings As Variant: sStrings = VBA.Array("AM", "PM") ' Search
    Dim rStrings As Variant: rStrings = VBA.Array(" AM", " PM") ' Replace
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        
        Dim rCount As Long: rCount = .Rows.Count
        
        Dim Data As Variant

        If rCount = 1 Then
            ReDim Data(1 To 1, 1 To 1): Data(1, 1) = .Value
        Else
            Data = .Value
        End If
        
        Dim nUpper As Long: nUpper = UBound(sStrings)
        
        Dim r As Long
        Dim n As Long
        Dim Pos As Long
        
        For r = 1 To rCount
            For n = 0 To nUpper
                Pos = InStr(1, Data(r, 1), sStrings(n), vbTextCompare)
                If Pos > 4 Then
                    Data(r, 1) = Trim(Left(Data(r, 1), Pos - 1)) & rStrings(n)
                    Exit For
                End If
            Next n
        Next r
        
        .Value = Data
    
    End With

End Sub

CodePudding user response：

Put this in the first row next to the column containing the times.

=LET(d,UPPER(A1:A4),am,SUBSTITUTE(d,"AM"," AM"),SUBSTITUTE(am,"PM"," PM"))

Change the "A1:A4" to be the range of your column containing the dates.

CodePudding user response：

Try this. If cell A1 has the data, this formula works fine.

=MID(TRIM(A1),1,LEN(TRIM(A1))-2) & " " & RIGHT(TRIM(A1),2)