Break up Date and Time in Cell

[Guzzlr]

Hello All,
I have a CSV file that prints out Date and Time in one cell: 1/4/2021 5:39 in column A
What I've been struggling with, is to break apart the Date and Time, and keep the Date in Column A, but place the Time in Column B.
I've not had any luck at this, can someone guide me please.
Thanks for the help

 

[sparky2205]

What about Text to Columns using Space as a delimiter.

 

[Guzzlr]

What about Text to Columns using Space as a delimiter.

Text to columns would work, but I was looking for VBA Code to include in the code there.
Is there a way to have this done in VBA?
thanks for the help

 

[Joe4]

Text to columns would work, but I was looking for VBA Code to include in the code there.
Is there a way to have this done in VBA?

Turn on your Macro Recorder, and record yourself performing the Text to Columns on this column, and you will have the VBA code that you need.

 

[Guzzlr]

that's what I did...sure seems messy though
Thanks for the help

 

[Joe4]

that's what I did...sure seems messy though

What's messy about it?
If you want the code cleaned up a bit, post what you recorded here, and we can help you simplify it.

 

[sparky2205]

Something like this.
You'll need to play around with ranges to make it right for you.

Sub Macro1()
    Selection.TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, _
    Space:=True
End Sub

 

[Guzzlr]

Something like this.
You'll need to play around with ranges to make it right for you.

Sub Macro1()
    Selection.TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, _
    Space:=True
End Sub

This worked well, thanks!

 

[Dave Patton]

The suggestion leaves the date as Text in the first column and the time in the second column.

Is it possible to make the original column Date+Time and format it in an Internationally recognized format say yyyymmdd hh:mm?

If extra columns are required, could they to the right of existing data?

 

[Joe4]

The suggestion leaves the date as Text in the first column and the time in the second column.

It doesn't for me, and you can explicitly tell it to be a date if you want, i.e.

Sub Macro1()
    Selection.TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, _
    Space:=True, _
    FieldInfo:=Array(Array(1, 3), Array(2, 3))
End Sub

I am guessing maybe it is different behaviors between US and European versions, as they sometimes handle dates a little differently.