Text to Column 1

[bcollinsmoose]

Is there a function that you can use that will perform the "Text to Column" command in Excel. I am trying to separte 1 column into 2 where the data is separated by a comma. I would like to do this using VBA.

Thank you,
Beth

 

[JustinEzequiel]

from Excel VBA help:

TextToColumns Method

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)

expression Required. An expression that returns a Range object.

Destination Optional Variant. 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 Optional Variant. The format of the text to be split into columns. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. The default value is xlDelimited.

TextQualifier Optional Variant. The text qualifier. Can be one of the following XlTextQualifier constants: xlTextQualifierDoubleQuote, xlTextQualifierSingleQuote, or xlTextQualifierNone. The default value is xlTextQualifierDoubleQuote.

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

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

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

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

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

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

OtherChar Optional Variant. (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 Optional Variant. 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 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 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. 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; 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))

BTW, I found this method by recording a macro
(Tools-->Macro-->Record New Macro)