TextToColumns Method

This method parses a column (or columns) of cells that contain text into several columns. The syntax is:

RangeObject.TextToColumns(Destination, DataType,

TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo)

Note that all of the parameters to this method are optional.

The Destination parameter is a Range object that specifies where to put the results of the conversion. If the Range object represents more than a single cell, then the starting point for the destination is the upper-left cell in that range.

The DataType parameter specifies the format of the text to be split into columns. It can be one of the following XlTextParsingType constants:

Enum XlTextParsingType xlDelimited = 1 ' Default xlFixedWidth = 2 End Enum

The TextQualifier parameter is the text qualifier. It can be one of the following

XlTextQualifier constants:

Enum XlTextQualifier xlTextQualifierNone = -4142

xlTextQualifierDoubleQuote = 1 ' Default xlTextQualifierSingleQuote = 2 End Enum

The ConsecutiveDelimiter parameter should be set to True to have Excel consider consecutive delimiters as one delimiter. The default value is False.

There are several parameters that require that the DataType be xlDelimited and, when set to True, indicate that Excel should use the corresponding character as the text delimiter. They are described in the following list (all default values are False):

Set to True to use the tab character as delimiter.

Semicolon

Set to True to use a semicolon as delimiter.

Comma

Set to True to use a comma as delimiter.

Space

Set to True to use a space as delimiter.

Other

Set to True to use a character that is specified by the OtherChar argument as delimiter.

When Other is True, OtherChar specifies the delimiter character. If OtherChar contains more than one character, only the first character is used.

The FieldInfo parameter is an array containing parse information for the individual source columns. The interpretation of FieldInfo depends on the value of DataType.

When DataType is xlDelimited, the FieldInfo argument should be an array whose size is the same as (or smaller than—see Table 19-3) the number of columns of converted data. The first element of a two-element array is the column number (starting with the number 1), and the second element is one of the numbers in Table 19-3 that specifies how the column is parsed.

Table 19-3. FieldInfo Values for xlDelimited Text

Code

Description

1

General

2

Text

3

MDY date

4

DMY date

5

YMD date

6

MYD date

7

DYM date

8

YDM date

9

Skip the column

If a two-element array for a given column is missing, then the column is parsed with the General setting. For instance, the following value for FieldInfo causes the first column to be parsed as text and the third column to be skipped:

All other columns will be parsed as general data.

To illustrate, consider the sheet shown in Figure 19-15. The code:

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

ConsecutiveDelimiter:=True, Comma:=True, FieldInfo:=Array(Array(1, 2), Array(2, 3))

produces the second and third columns of Figure 19-15. Note that the cells in column C are formatted as dates.

Figure 19-15. A worksheet with text to be parsed in A1 :A3

1 book, 12/4/98 book

2 record. 1/17/93 record

On the other hand, if DataType is xlFixedWidth, the first element of each two-element array specifies the starting character position in the column (0 being the first character) and the second element specifies the parse option (1-9) for the resulting column, as described previously.

To illustrate, consider the worksheet in Figure 19-16. The code:

Range("A1:A3").TextToColumns _ Destination:=Range("B1"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2),

Array(1, 9), Array(2, 2), Array(5, 9), Array(6, 2))

parses the first column of Figure 19-16 into the remaining columns. (Note how we included arrays to skip the hyphens.)

Figure 19-16. A worksheet with fixed-width data to be parsed in A1 :A3

A

B

C

D

f

1-234-567

1

234

557

2

2-436-678

2

435

67o

3

5-444-666

5

444

666

+1 -3

Post a comment