Text to column

Hi Ferdous

Thanks for the question.

The easiest way to confirm this syntax is to record it but its not the most clear code.

A simple example of dividing text into 3 columns with commas seperating each item looked like:

    

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _

 FieldInfo :=Array(Array(1, 1), Array(2, 2), Array(3, 1)), _

TrailingMinusNumbers:=True

The most confusing part of it is the FieldInfo. It requires an array to be built with pairs of values for each column. The first value is the column number the second the data type.

Data Types are

1 = xlTextFormat. Text
2 = xlMDYFormat. MDY Date
3 = xlDMYFormat. DMY Date
4 = xlYMDFormat. YMD Date
5 = xlMYDFormat. MYD Date
7 = xlDYMFormat. DYM Date
8 = xlYDMFormat. YDM Date
9 = xlSkipColumn. Skip Column

I hope that helps. Let me know how you get on.

Laura GB