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: 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!

Why would you do the text to columns here? Dates and time are numbers for excels. Dates are integers and time are floating numbers for the date... so if you want to only show de date and not by format, Roundown the numbers for every date leaving only the date without time. — Damian, Oct 23 '19 at 15:20
Possible duplicate of [VBA Text-To-Columns Reading Date Format As US](https://stackoverflow.com/questions/26119089/vba-text-to-columns-reading-date-format-as-us) — SeanC, Oct 23 '19 at 15:23
Looks like you're trying to split at the space? Yet you have `Comma:=True, Space:=False`. Have you tried `Comma:=False, Space:=True`? That said, seems all that's needed is to parse the date string into a `Date` value, overwrite the string with the `Date`, and then use number formatting to remove the time portion. — Mathieu Guindon, Oct 23 '19 at 15:31

score 0 · Answer 1 · answered Oct 29 '19 at 14:23

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.

VBA TextToColumns - Date format

1 Answers1