VBA TextToColumns - Date format

Question

I have a problem with macro TextToColumns: I have two date formats, one with 01:00 at the end and the other with 03:00 at the end, for example:

14/10/2019 03:00

08/06/2019 01:00

When I run macro:

    Worksheet.Columns("A:A").TextToColumns Destination:=Worksheet.Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, DecimalSeparator:="."

I have results:

I need to have all the data in format like the bottom one, but I can't do it for example by number -> format -> custom.

I would really appreciate if you have any ideas what is wrong with my macro?

Thanks!

Answer 1

Currently you have it set to split on commas instead of spaces which would explain why nothing is getting separated.

When it does split you are given four columns of data and so I've written some code that will create and delete temporary columns to protect and data you might have in the cells to the right.

The format of the output column is also set to general so it defaults to date and doesn't show a blank time at the end.

With Sheets("Sheet1")
    .Columns("B").NumberFormat = "General"
    .Columns("C:E").Insert Shift:=xlToRight
    .Columns("A").TextToColumns Destination:=.Columns("B"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, DecimalSeparator:="."
    .Columns("C:E").Delete
End With

If you need any help with it or a further explanation just leave a comment and I'll get back to you.