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
Cross-posted on Excel Forums - Was not getting much traction there so I decided to cross-post here.
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.