NamedRange.TextToColumns Method

Reference

Definition

Namespace:: Microsoft.Office.Tools.Excel

Assembly:: Microsoft.Office.Tools.Excel.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

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

public object TextToColumns (object Destination, Microsoft.Office.Interop.Excel.XlTextParsingType DataType = Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier TextQualifier = Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, object ConsecutiveDelimiter, object Tab, object Semicolon, object Comma, object Space, object Other, object OtherChar, object FieldInfo, object DecimalSeparator, object ThousandsSeparator, object TrailingMinusNumbers);

abstract member TextToColumns : obj * Microsoft.Office.Interop.Excel.XlTextParsingType * Microsoft.Office.Interop.Excel.XlTextQualifier * obj * obj * obj * obj * obj * obj * obj * obj * obj * obj * obj -> obj

Public Function TextToColumns (Optional Destination As Object, Optional DataType As XlTextParsingType = Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Optional TextQualifier As XlTextQualifier = Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, Optional ConsecutiveDelimiter As Object, Optional Tab As Object, Optional Semicolon As Object, Optional Comma As Object, Optional Space As Object, Optional Other As Object, Optional OtherChar As Object, Optional FieldInfo As Object, Optional DecimalSeparator As Object, Optional ThousandsSeparator As Object, Optional TrailingMinusNumbers As Object) As Object

Parameters

Destination: Object

A Range object that specifies where Microsoft Office Excel will place the results. If the range is larger than a single cell, the top left cell is used.

DataType: XlTextParsingType

The format of the text to be split into columns.Can be one of the following XlTextParsingType constants: xlDelimited (default) xlFixedWidth

TextQualifier: XlTextQualifier

Can be one of the following XlTextQualifier values: xlTextQualifierDoubleQuote (default) xlTextQualifierNone xlTextQualifierSingleQuote

ConsecutiveDelimiter: Object

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

Tab: Object

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

Semicolon: Object

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

Comma: Object

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

Space: Object

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

Other: 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: 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: 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: Object

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

ThousandsSeparator: Object

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

TrailingMinusNumbers: Object

Numbers that begin with a minus character.

Returns

Object

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 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);
}

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

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.

Applies to

Share via