Applies To
Range Object.
Description
Parses a column of cells containing text into several columns
Syntax
object.TextToColumns(destination, dataType, textQualifier, consecutiveDelimiter, tab, semicolon, comma, space, other, otherChar, fieldInfo)
object
Required. The Range object.
destination
Optional. A range that specifies where Microsoft Excel will place the results. If the range is larger than a single cell, the top leftmost cell is used.
dataType
Optional. Specifies the format of the text to split into columns. Can be either xlDelimited or xlFixedWidth. The default is xlDelimited.
textQualifier
Optional. Specifies the text qualifier. Can be one of xlDoubleQuote, xlSingleQuote, or xlNone. The default is xlDoubleQuote.
consecutiveDelimiter
Optional. True if consecutive delimiters should be considered as one delimiter. The default is False.
tab
Optional. True if dataType is xlDelimited and the tab character is a delimiter. The default is False.
semicolon
Optional. True if dataType is xlDelimited and the semicolon character is a delimiter. The default is False.
comma
Optional. True if dataType is xlDelimited and the comma character is a delimiter. The default is False.
space
Optional. True if dataType is xlDelimited and the space character is a delimiter. The default is False.
other
Optional. True if dataType is xlDelimited and the character specified by the otherChar argument is a delimiter. The default is False.
otherChar
Optional (required if other is True). Specifies the delimiter character when other is True. If more than one character is specified, only the first character of the string is used, remaining characters are ignored.
fieldInfo
Optional. An array containing parse information for the individual columns of data. The interpretation depends on the value of dataType.
When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (one based), and the second element is one of the following numbers specifying how the column is parsed:
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
The column specifiers may be in any order. If a column specifier is not present for a particular column in the input data, the column is parsed using the General setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.
Array(Array(3, 9), Array(1, 2))
If the source data has fixed-width columns, the first element of each two-element array specifies the starting character position in the column (as an integer; character zero is the first character). The second element of the two-element array specifies the parse option for the column as a number from one through nine, as listed above.
The following example parses two columns from a fixed-width file, with the first column starting at the beginning of the line and extending for 10 characters. The second column starts at position 15 and goes to the end of the line. To avoid including the characters between position 10 and position 15, a skipped column entry is added.
Array(Array(0, 1), Array(10, 9), Array(15, 1))
See Also
OpenText Method.
Example
This example converts the contents of the Clipboard, which contains a space-delimited text table, into separate columns on Sheet1. You can create a simple space-delimited table in Notepad or WordPad (or another text editor), copy the text table to the Clipboard, switch to Microsoft Excel, and then run this example.
Worksheets("Sheet1").Activate
ActiveSheet.Paste
Selection.TextToColumns DataType:=xlDelimited, _
consecutiveDelimiter:=True, Space:=True