TEXT To Columns, strange effect of a macro

[LGMan]

Hi, following thread68 1763485, I was able to use the Data > Text to columns to split out a DD-MMM-YY HH:MM:SS format into 2 columns, Col A for the date and Col B for the time.
This works fine if done manually, following the Fixed Width option and not importing the empty space between the data and time.
However when I recorded a macro and reran it, instead of splitting the data over 2 columns, it splits it over 3, has the date and 00:00:00 in col A, drops the hour in Col B and has AM or PM in Col C

here is my orginal display
26-Mar-16 09:26:00
26-Mar-16 13:27:18

Here is how is looks spread over 3 cells
[highlight]26-Mar-16 00:00:00[/highlight]|[highlight]:26:00[/highlight]|[highlight] AM[/highlight]
[highlight]26-Mar-16 00:00:00[/highlight]|[highlight]:27:18[/highlight]|[highlight] PM[/highlight]

Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(10, 9), Array(12, 1)), TrailingMinusNumbers _
        :=True

This is first time that I've seen a recorded macro work differently to the effect of the manual steps on the same data.
Any ideas on what needs to change in the code to get the date and time to split over 2 columns?

 

[SkipVought]

Hi,

Turn on your macro recorder and record the following steps:

Select the column containing the Date/Time

Change the Number Firmat to GENERAL

Open Text to columns

DELIMITED

PERIOD (decimal point)

Change the Number Format on the Date and Time columns as required


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You are working with date/time values, together with numbers VBA sees them differently (VBA uses US settings). Try conversion to text and next apply TextToColumns:

Application.CutCopyMode = False
Dim cell As Range, strTmp As String
For Each cell In Selection
    strTmp = cell.Text
    cell.NumberFormat = "@"
    cell.Value = strTmp
Next cell
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(11, 9), Array(13, 1)), TrailingMinusNumbers _
        :=True

combo

 

[LGMan]

Great, thanks to you both Skip and Combo.