Text To Columns Tool

The Text To Columns tool takes the text in one column and splits the string value into separate, multiple columns (or rows), based on a single or multiple delimiter(s).

Configure the tool

  1. Select the Field to Split.
  2. Delimiters: Enter the delimiter (s) to use to split the data on. Each character is treated independently. You can type the character in this space or use the following:

    \t Tab
    \n New line
    \s Space
    \s\t Space or Tab
  3. Choose your method for splitting:
    • Split to Columns: Split a single column of data at each instance of the specified delimiter into multiple columns.
      • # of Columns: Set how many columns are created.
      • Extra Columns: Select the behavior that is applied to extra columns.
        • Leave Extra in Last Field: Data that extends past the split is appended to the value in the last column.
        • Drop Extra with Warning: Data that extends past the split is dropped and a warning is generated indicating that there was excess information.
        • Drop Extra without Warning: Data that extends past the split is dropped and no warning is generated.
        • Error: Data the extends past the split causes an error and the workflow stops processing.
      • Output Root Name: Enter the name for the newly generated columns to be based off of. The new columns are named as the root name with a serially increasing integer appended.
    • Split to Rows: Split a single column of data at each instance of the specified delimiter into multiple rows. Use a key column in your record so you don't lose track of which value came from which row.

Advanced Options: Select delimiters to ignore. Options include:

  • Ignore Delimiters in Quotes
  • Ignore Delimiters in Single Quotes
  • Ignore Delimiters in Parenthesis
  • Ignore Delimiters in Brackets
  • Skip Empty Fields