Converting Text String to Date Format

[MaxxLevell]

I have a csv that is showing the following text in a date column... October 30 2020 10:22 am How can I convert this to a date format? I have tried, unsuccessfully, using the Value() function, but it still does not recognize it. Any help would be appreciated. I feel like I'm missing something simple.

 

[JGordon11]

Add a comma after the day (in this case October 30, 2020 10:22 am) and it should convert automatically

 

[MaxxLevell]

Add a comma after the day (in this case October 30, 2020 10:22 am) and it should convert automatically

Thank you...that worked on the field I tried it on. Now my issue is how to add a comma after the second "word" in each string. I've found lots of info on how to substitute a comma after the first word, or after every word...but, I can't seem to figure out how to just add one comma after the second word. I have nearly 30,000 records I need to modify so they show properly.

 

[JGordon11]

If you can use VBA then select the range to work on (code assumes a single column of data) and run the following macro (on a test copy of your sheet):

Sub AddComma()
    Dim t, r, i As Long, ubr As Long
    r = Selection.Value
    ubr = UBound(r)
    For i = 1 To ubr
        t = Split(r(i, 1), " ")
        t(1) = t(1) & ","
        r(i, 1) = Join(t, " ")
    Next
    Selection = r
End Sub

 

[Sufiyan97]

Try

and format cells as - mm/dd/yyyy h:mm

=SUBSTITUTE(A1," ",", ",2)+0

 

[MaxxLevell]

If you can use VBA then select the range to work on (code assumes a single column of data) and run the following macro (on a test copy of your sheet):

Sub AddComma()
    Dim t, r, i As Long, ubr As Long
    r = Selection.Value
    ubr = UBound(r)
    For i = 1 To ubr
        t = Split(r(i, 1), " ")
        t(1) = t(1) & ","
        r(i, 1) = Join(t, " ")
    Next
    Selection = r
End Sub

Thank you for your assistance on this...it worked perfectly.