Macro: Text to Columns

[CodenameAter]

I have created a macro that selects everything in a sheet starting on A4 downwards. I want to use Text to Columns, delimited (comma and space) to separate the data. When I do it manually, it does not change the date into a date format but keeps it as text (i.e. 05/30/21 stay as 05/30/21 instead of changing to 44346.0). However, the time values below the dates are in a numerical form (left side of the picture).

When I run the macro I created following the exact same manual process, it changes the dates to the date format (right side).

I want the macro to produce the same exact same results when I run it manually (i.e. 05/30/21 stays as 05/30/21 but the time values are in a numerical format).

Is there a way I can edit the VBA code below to produce this result?

Thank you

Sub Text2Colv4()
'
' Text2Colv4 Macro
'

'
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7 _
        , 2)), TrailingMinusNumbers:=True
End Sub

Cross-posted on Excel Forums - Was not getting much traction there so I decided to cross-post here.

 

[rlv01]

Show us an example of what the input CSV text data looks like, before you run TextToColumns on it.

 

[CodenameAter]

Here is what it looks like:

KING,HBD,05/23/21,scan
MYNL ,10:41:51 ,
MYNL MMWO ,10:41:59 ,
MMWO MYNW FBWR rattle ,10:45:08 ,
MMLH FDRY ,10:45:26 ,
MYHW MPPW ,10:45:53 ,
FDBR rattle ,10:46:35 ,

 

[rlv01]

I'm not seeing the same issue. When I test using your code as-is using your data, the results are all text strings.

If I change the data conversion type, by changing the array statement

   Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
    , 1)), TrailingMinusNumbers:=True

to let Excel guess at the type I get

 

[CodenameAter]

That is very strange. When I run the Macro, I get your second picture where it converts both date and time to their respective formats. Whereas if I do the steps manually, I get everything to string but the time values, where they are recognized as numerical.

Would it have to do with clock settings in Windows or something like that?

 

[rlv01]

What Excel version?

 

[CodenameAter]

Is this the info you want?

MS365 MSO (16.0.14026.20202) 64-bit

 

[CodenameAter]

Here's another idea. I believe if a number is great than 1, it is a date and if it less than 1, then it is a time with a date of =0.

Is there a way to tell excel to format a column as a date if the value is greater than 1 and as a time value if it is less than 1?

 

[rlv01]

Probably there is, but it's not related to TextToColumns so you should start a new thread for that.

 

[CodenameAter]

Will do