NamedRange.TextToColumns Method

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

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.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
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

  • ConsecutiveDelimiter
    Type: System.Object

    true to have Excel consider consecutive delimiters as one delimiter. The default value is false.

  • Tab
    Type: System.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

    true to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is false.

  • Comma
    Type: System.Object

    true to have DataType be xlDelimited and to have the comma be a delimiter. The default value is false.

  • Space
    Type: System.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

    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

    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

    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 values:

    xlGeneralFormat

    xlTextFormat

    xlMDYFormat

    xlDMYFormat

    xlYMDFormat

    xlMYDFormat

    xlDYMFormat

    xlYDMFormat

    xlEMDFormat

    xlSkipColumn

    You can use xlEMDFormat only if Taiwanese language support is installed and selected. xlEMDFormat 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.

    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

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

  • ThousandsSeparator
    Type: System.Object

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

  • TrailingMinusNumbers
    Type: System.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)

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Examples

The following code example uses the TextToColumns method to convert a space-delimited date string in a NamedRange to three columns.

This example is for a document-level customization.

Private Sub ConvertTextToColumns()
        Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
            = Me.Controls.AddNamedRange(Me.Range("A1"), _
            "namedRange1")

        namedRange1.Value2 = "01 01 2001" 
        Dim destinationRange As Excel.Range = Me.Range("A5")

        namedRange1.TextToColumns(destinationRange, _
            Excel.XlTextParsingType.xlDelimited, _
            Excel.XlTextQualifier.xlTextQualifierDoubleQuote, , , , , _
            True, , , , , , )
    End Sub
private void ConvertTextToColumns()
{
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(this.Range["A1"],
        "namedRange1");

    namedRange1.Value2 = "01 01 2001";
    Excel.Range destinationRange = this.Range["A5"];

    namedRange1.TextToColumns(destinationRange,
        Excel.XlTextParsingType.xlDelimited,
        Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
         true);
}

.NET Framework Security

See Also

Reference

NamedRange Interface

Microsoft.Office.Tools.Excel Namespace