Skip to main content

Text to Columns Tool

Use Text To Columns to take the text in 1 column and split the string value into multiple separate columns or rows, based on a 1 or more delimiters.

Tip

This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.

Configure the Tool

  1. Select the Column to Split.

  2. Delimiters: Enter the delimiters you want to use to split the data. Each character is treated independently, meaning you can't delimit on a word. You can use individual characters or the white space characters from this table:

    White Space

    White Space Character

    Tab

    \t

    New Line

    \n

    Space

    \s

    Space or Tab

    \s\t
  3. Choose the split method:

    • Split into Columns: Split a single column of data at each instance of the specified delimiter into multiple columns.

      • Number of Output Columns: Choose how many columns to create.

      • Output Column Name: Enter the name for the newly generated columns. The new columns are named as the root name with a serially increasing integer appended.

      • Extra Characters: Choose what you want to do with any extra characters.

        • Leave in Last Column: Data that extends past the split is appended to the value in the last column.

        • Drop with Warning: Data that extends past the split is dropped and a warning is generated indicating that there is excess information.

        • Drop without Warning: Data that extends past the split is dropped and no warning is generated.

    • Split into Rows: Split a single column of data at each instance of the specified delimiter into multiple rows. Use a key column in your row so you don't lose track of what value comes from what row.

  4. Select Delimiters to Ignore:

    • Consecutive: For Split into Rows, empty rows are not created for consecutive delimiters. For Split into Columns, empty columns are populated with Null.

    • None: All delimiters are recognized.

    • In Quotation Marks: Delimiters and surrounding quotation marks are included in the cell text.

    • In Single Quotation Marks: Delimiters and surrounding single quotation marks are included in the cell text.