Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
TextToColumns Method

Range.TextToColumns Method

Office 2007
Parses a column of cells that contain text into several columns.

Syntax

expression.TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

expression   A variable that represents a Range object.

Parameters

NameRequired/OptionalData TypeDescription
DestinationOptionalVariantA Range object that specifies where Microsoft Excel will place the results. If the range is larger than a single cell, the top left cell is used.
DataTypeOptionalXlTextParsingTypeThe format of the text to be split into columns.
TextQualifierOptionalXlTextQualifierSpecifies whether to use single, double or no quotes as the text qualifier.
ConsecutiveDelimiterOptionalVariantTrue to have Microsoft Excel consider consecutive delimiters as one delimiter. The default value is False.
TabOptionalVariantTrue to have DataType be xlDelimited and to have the tab character be a delimiter. The default value is False.
SemicolonOptionalVariantTrue to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is False.
CommaOptionalVariantTrue to have DataType be xlDelimited and to have the comma be a delimiter. The default value is False.
SpaceOptionalVariantTrue to have DataType be xlDelimited and to have the space character be a delimiter. The default value is False.
OtherOptionalVariantTrue to have DataType be xlDelimited and to have the character specified by the OtherChar argument be a delimiter. The default value is False.
OtherCharOptionalVariant(required if Other is True). The delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
FieldInfoOptionalVariantAn 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 (1-based), and the second element is one of the xlColumnDataType constants specifying how the column is parsed.
DecimalSeparatorOptionalVariantThe decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.
ThousandsSeparatorOptionalVariantThe thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.
TrailingMinusNumbersOptionalVariantNumbers that begin with a minus character.

Return Value
Variant

Remarks

The following table shows the results of importing text into Excel for various import settings. Numeric results are displayed in the rightmost column.

System decimal separatorSystem thousands separatorDecimal separator valueThousands separator valueOriginal textCell value (data type)
PeriodCommaCommaPeriod123.123,45123,123.45 (numeric)
PeriodCommaCommaComma123.123,45123.123,45 (text)
CommaPeriodCommaPeriod123,123.45123,123.45 (numeric)
PeriodCommaPeriodComma123 123.45123 123.45 (text)
PeriodCommaPeriodSpace123 123.45123,123.45 (numeric)
XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat. General
xlTextFormat. Text
xlMDYFormat. MDY Date
xlDMYFormat. DMY Date
xlYMDFormat. YMD Date
xlMYDFormat. MYD Date
xlDYMFormat. DYM Date
xlYDMFormat. YDM Date
xlEMDFormat. EMD Date
xlSkipColumn. Skip Column

You can use xlEMDFormat only if Taiwanese language support is installed and selected. The xlEMDFormat constant specifies that Taiwanese era dates are being used.

The column specifiers can be in any order. If a given column specifier is not present for a particular column in the input data, the column is parsed with the xlGeneralFormat 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 xlGeneralFormat 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; 0 (zero) is the first character). The second element of the two-element array specifies the parse option for the column as a number from 1 through 9, 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, Microsoft Excel adds a skipped column entry.

Array(Array(0, 1), Array(10, 9), Array(15, 1))

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.

Visual Basic for Applications
Worksheets("Sheet1").Activate
ActiveSheet.Paste
Selection.TextToColumns DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, Space:=True



Community Additions

ADD
Show:
© 2015 Microsoft