Insert a space before AM or PM in a text column

[Crocdundee]

Hi I have a column of text which really is a date and time combined, however I can not sort correctly with this format.
let me show you the data
11:01AM 23 Aug 2021
11:22AM 23 Aug 2021
11:43AM 23 Aug 2021
12:04PM 23 Aug 2021
12:53PM 23 Aug 2021
1:33PM 23 Aug 2021
2:12PM 23 Aug 2021
2:48PM 23 Aug 2021
1:00PM 23 Aug 2021
1:30PM 23 Aug 2021
2:00PM 23 Aug 2021
2:30PM 23 Aug 2021
3:00PM 23 Aug 2021
Data is in column B2:B
In Reality I need a macro to put a space before the AM or PM
Regards Graham

 

[veyselemre]

Hi,
try this..

Sub replace()
    With Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
        .replace "AM", " AM"
        .replace "PM", " PM"
    End With
End Sub

 

[Rick Rothstein]

Hi,
try this..

Sub replace()
    With Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
        .replace "AM", " AM"
        .replace "PM", " PM"
    End With
End Sub

While the above code will work as written, I would suggest you use a different macro name as Replace is a built-in function in VBA.

 

[veyselemre]

While the above code will work as written, I would suggest you use a different macro name as Replace is a built-in function in VBA.

You are so right, it escaped my attention.

 

[Crocdundee]

Thank you All for answering my Vba question, I always try myself to solve my problems, but this one escaped me
I appreciate all the help that is given here
Regards
Graham