CODEWITHSUNDEEP
excelvba
Selrac
Selrac

Reputation: 2293

Convert text to columns issue with date format MMM-YY

I'm doing a conversion using text to columns in Excel on a text like:

NAME|PERIOD_NAME|AMOUNT|CREATION_DATE
ANDY|OCT-18|6385.46|17-OCT-2018

I'm using the following code

Columns("A:A").TextToColumns Destination:=Range("A1"), _ 
    DataType:=xlDelimited, Other:=True, OtherChar:="|"

The problem is that the output on the dates with format MMM-YY turns like YY-MMM-YY

Date format

Any ideas on what is causing the issue or how can the VBA code be formatted to correct this issue?

Upvotes: 0

Views: 765

Answers (1)

oxwilder
oxwilder

Reputation: 757

One of the parameters for .textToColumns is dataType in which you can set all the parsed data to a string or integer or whatever you choose, and in this case Excel is automatically converting a string to a date. To specify a different data type for each column, you create an array of delimited dataTypes that correspond to each field. They're enumerated here on Microsoft's VBA page here:

Hope that helps.

Upvotes: 1

Related Questions