Equivalent to clicking the Text To Columns command on the Data menu when a column of data is to be separated into multiple columns. Parses text into columns of data.
Syntax
TEXT.TO.COLUMNS(destination_ref, data_type, text_delim, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info)
The following arguments correspond to check boxes, option buttons and text buttons in the Text To Columns Wizard, which is started with the Text To Columns command on the Data menu.
Destination_Ref is a single cell reference to specifies where to place the converted text.
Data_Type is a number specifying whether that data is delimited (1) or fixed width (2)
Text_Delim denotes how text strings are represented, and can be one of the following values:
| Number | Text_Delim |
| 1 | " |
| 2 | ' |
| 3 | none |
Consecutive_delim is a logical value corresponding to the Treat Consecutive Delimiters as One check box which, if TRUE, allows consecutive delimiters (such as ",,,") to be treated as a single delimiter. If FALSE, all consecutive delimiters are considered separate column breaks.
Tab is a logical value which, if TRUE, specifies that the column has tab delimiters. If FALSE, the column does not have tab delimiters. Tab is ignored if data_type is 2.
Semicolon is a logical value which, if TRUE, specifies that the column has semicolon delimiters. If FALSE, the column does not have semicolon delimiters. Semicolon is ignored if data_type is 2.
Comma is a logical value which, if TRUE, specifies that the column has comma delimiters. If FALSE, the column does not have comma delimiters. Comma is ignored if data_type is 2.
Space is a logical value which, if TRUE, specifies that the column has space delimiters. If FALSE, the column does not have space delimiters. Space is ignored if data_type is 2.
Other is a logical value which, if TRUE, specifies that the column has custom delimiters. If FALSE, the column does not have custom delimiters. Other is ignored if data_type is 2.
Other_Char is a single character that specifies a delimiter not included in the list of delimiters. Other_Char is ignored if data_type is 2 and if the argument other is FALSE.
Field_Info is an array which consists of the following elements: "column number, data_format", if data_type is 1; or "start_pos, data_format" if data_type is 2. The second number defines the column's data format, and can be one of the following.
| 2nd Number | Data Format |
| 1 | General |
| 2 | Text |
| 3 | Date, in the form MDY |
| 4 | Date, in the form DMY |
| 5 | Date, in the form YMD |
| 6 | Date, in the form MYD |
| 7 | Date, in the form DYM |
| 8 | Date, in the form YDM |
| 9 | Do not import column (skip) |
Return to index