Range.TextToColumns Method

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

Namespace:  Microsoft.Office.Interop.Excel
Assembly:  Microsoft.Office.Interop.Excel (in Microsoft.Office.Interop.Excel.dll)

Syntax

'Declaration
Function TextToColumns ( _
    Destination As Object, _
    DataType As XlTextParsingType, _
    TextQualifier As XlTextQualifier, _
    ConsecutiveDelimiter As Object, _
    Tab As Object, _
    Semicolon As Object, _
    Comma As Object, _
    Space As Object, _
    Other As Object, _
    OtherChar As Object, _
    FieldInfo As Object, _
    DecimalSeparator As Object, _
    ThousandsSeparator As Object, _
    TrailingMinusNumbers As Object _
) As Object
'Usage
Dim instance As Range
Dim Destination As Object
Dim DataType As XlTextParsingType
Dim TextQualifier As XlTextQualifier
Dim ConsecutiveDelimiter As Object
Dim Tab As Object
Dim Semicolon As Object
Dim Comma As Object
Dim Space As Object
Dim Other As Object
Dim OtherChar As Object
Dim FieldInfo As Object
Dim DecimalSeparator As Object
Dim ThousandsSeparator As Object
Dim TrailingMinusNumbers As Object
Dim returnValue As Object

returnValue = instance.TextToColumns(Destination, _
    DataType, TextQualifier, ConsecutiveDelimiter, _
    Tab, Semicolon, Comma, Space, Other, _
    OtherChar, FieldInfo, DecimalSeparator, _
    ThousandsSeparator, TrailingMinusNumbers)

Object TextToColumns(
    Object Destination,
    XlTextParsingType DataType,
    XlTextQualifier TextQualifier,
    Object ConsecutiveDelimiter,
    Object Tab,
    Object Semicolon,
    Object Comma,
    Object Space,
    Object Other,
    Object OtherChar,
    Object FieldInfo,
    Object DecimalSeparator,
    Object ThousandsSeparator,
    Object TrailingMinusNumbers
)

Parameters

• Destination
  Type: System.Object

  Optional Object. A 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.

• DataType
  Type: Microsoft.Office.Interop.Excel.XlTextParsingType

  Optional XlTextParsingType. The format of the text to be split into columns. Can be one of the following XlTextParsingType constants:

  • xlDelimited default

  • xlFixedWidth

• TextQualifier
  Type: Microsoft.Office.Interop.Excel.XlTextQualifier

  Optional XlTextQualifier. Can be one of the following XlTextQualifier constants:

  • xlTextQualifierDoubleQuotedefault

  • xlTextQualifierNone

  • xlTextQualifierSingleQuote

• ConsecutiveDelimiter
  Type: System.Object

  Optional Object. True to have Microsoft Excel consider consecutive delimiters as one delimiter. The default value is False.

• Tab
  Type: System.Object

  Optional Object. True to have DataType be xlDelimited and to have the tab character be a delimiter. The default value is False.

• Semicolon
  Type: System.Object

  Optional Object. True to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is False.

• Comma
  Type: System.Object

  Optional Object. True to have DataType be xlDelimited and to have the comma be a delimiter. The default value is False.

• Space
  Type: System.Object

  Optional Object. True to have DataType be xlDelimited and to have the space character be a delimiter. The default value is False.

• Other
  Type: System.Object

  Optional Object. True to have DataType be xlDelimited and to have the character specified by the OtherChar argument be a delimiter. The default value is False.

• OtherChar
  Type: System.Object

  Optional Object (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.

• FieldInfo
  Type: System.Object

  Optional Object. 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 (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed. Can be one of the following XlColumnDataType constants:

  • xlGeneralFormat. Genera.

  • 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 General setting.

  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.

• DecimalSeparator
  Type: System.Object

  Optional String. The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.

• ThousandsSeparator
  Type: System.Object

  Optional String. The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.

• TrailingMinusNumbers
  Type: System.Object

  Optional Object. Numbers that begin with a minus character.

Return Value

Type: System.Object

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 separator	System thousands separator	Decimal separator value	Thousands separator value	Original text	Cell value (data type)
Period	Comma	Comma	Period	123.123,45	123,123.45 (numeric)
Period	Comma	Comma	Comma	123.123,45	123.123,45 (text)
Comma	Period	Comma	Period	123,123.45	123,123.45 (numeric)
Period	Comma	Period	Comma	123 123.45	123 123.45 (text)
Period	Comma	Period	Space	123 123.45	123,123.45 (numeric)

See Also

Reference

Range Interface

Range Members

Microsoft.Office.Interop.Excel Namespace