Macro: Text to Columns

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
30
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 :)

VBA Code:
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.
 

Attachments

  • MrExcel Upload Data Example.png
    MrExcel Upload Data Example.png
    62.6 KB · Views: 10

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

rlv01

Well-known Member
Joined
May 16, 2017
Messages
972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Show us an example of what the input CSV text data looks like, before you run TextToColumns on it.
 

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
30
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

Well-known Member
Joined
May 16, 2017
Messages
972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'm not seeing the same issue. When I test using your code as-is using your data, the results are all text strings.

1622579533578.png


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

VBA Code:
   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

1622579624555.png
 
Solution

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
30

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What Excel version?
 

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
30

ADVERTISEMENT

Is this the info you want?

MS365 MSO (16.0.14026.20202) 64-bit
 

CodenameAter

New Member
Joined
Jul 23, 2014
Messages
30
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

Well-known Member
Joined
May 16, 2017
Messages
972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Probably there is, but it's not related to TextToColumns so you should start a new thread for that.
 

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top