6.1 Text to Columns Tool

Quick Answer: The Text to Columns tool (Parse palette) splits string data using specified delimiters into either multiple columns (horizontal expansion with sequential naming [Field]1, [Field]2...) or multiple rows (vertical record multiplication). When splitting to columns, if the data contains more delimiters than target columns, you must select one of four Extra Characters handling behaviors: (1) Leave extra characters in last column (default), (2) Drop extra characters and Warn, (3) Drop extra characters without Warning, or (4) Error. In the Delimiters field, multiple characters are treated as individual single-character delimiters (OR logic), and special escape characters like \t (tab) and \n (newline) are fully supported.

Parsing semi-structured text is one of the most common data preparation tasks in Alteryx Designer. Whether untangling comma-separated address fields, breaking down log strings, or normalizing multi-valued tag lists, the Text to Columns tool provides foundational parsing capabilities without requiring complex regular expressions.

---

Tool Architecture & Anchor Specifications

The Text to Columns tool operates with a simple, standard tool architecture:

+-----------------------------------------------------------------------------+
|                        TEXT TO COLUMNS ARCHITECTURE                         |
+-----------------------------------------------------------------------------+
|                                                                             |
|   Incoming Stream                     Parsed Stream                         |
|   [Input Data] -------> ( I ) [ Text to Columns ] ( O ) -------> [Browse]   |
|                                                                             |
|   - 1 Input Anchor ( I )               - 1 Output Anchor ( O )               |
|   - Requires 1 String/WString field    - Appends parsed columns OR          |
|   - Passes all other fields untouched    multiplies records across rows     |
+-----------------------------------------------------------------------------+

• Input Anchor (I): Accepts a single incoming data stream. The tool requires at least one string-based column (String, V_String, WString, or V_WString) to serve as the target parsing field.
• Output Anchor (O): Emits the transformed data stream. Depending on the operational mode, it either retains the original record count with newly appended columns or increases the total record count through vertical row generation.

---

Operational Mode 1: Split to Columns (Horizontal Parsing)

In Split to columns mode, the tool decomposes a single delimited string into a fixed number of newly created columns appended to the right side of the dataset.

+-----------------------------------------------------------------------------+
|                  TEXT TO COLUMNS CONFIGURATION: COLUMNS                     |
+-----------------------------------------------------------------------------+
|  Select Column to Split:       [ Full_Address           |v]                 |
|  Delimiters:                   [ ,                        ]                 |
|                                                                             |
|  (*) Split to columns                                                       |
|      Number of columns:        [ 3                        ]                 |
|      Extra Characters:         [ Leave in last column   |v]                 |
|      Output Root Name:         [ Address_Part             ]                 |
|                                                                             |
|  ( ) Split to rows                                                          |
+-----------------------------------------------------------------------------+

Key Configuration Parameters

1. Select Column to split: Choose the target string field containing the delimited values.
2. Delimiters: Enter the character(s) used to separate values. Defaults to a comma (,).
3. Number of columns: Specify the exact integer count of new output fields to generate (e.g., 3).
4. Output root name: The prefix used to name generated columns. If the root name is Address_Part and Number of columns is 3, Alteryx generates Address_Part1, Address_Part2, and Address_Part3.
5. Extra Characters Drop-down: Determines how the engine behaves when a record contains more delimiters than the specified number of columns.

The 4 Extra Characters Handling Options

When a row contains more delimiter tokens than the configured "Number of columns", Alteryx must decide what to do with the excess text. The Core exam frequently tests the exact behavior of these four options:

EXAMPLE: Splitting '100 Main St, Suite 400, Austin, TX' with Number of Columns = 3
Delimiter = ',' | Root Name = 'Col'

1. Leave extra in last column:  Col1: '100 Main St' | Col2: ' Suite 400' | Col3: ' Austin, TX'
2. Drop extra and Warn:         Col1: '100 Main St' | Col2: ' Suite 400' | Col3: ' Austin' (Logs Warning)
3. Drop extra without Warning:  Col1: '100 Main St' | Col2: ' Suite 400' | Col3: ' Austin' (Silent)
4. Error:                       Workflow halts with fatal error on this record.

Exam Trap — Insufficient Columns: What happens if a record has fewer delimiters than the configured number of columns? Alteryx populates the available columns from left to right and fills the remaining rightmost columns with Null (or empty strings depending on incoming data). No error or warning is raised for having fewer tokens.

---

Operational Mode 2: Split to Rows (Vertical Normalization)

In Split to rows mode, the tool parses the delimited text vertically, generating a new record for every delimited token found in the target string. This process is known as record multiplication or unpivoting delimited lists.

+-----------------------------------------------------------------------------+
|                    SPLIT TO ROWS: RECORD MULTIPLICATION                     |
+-----------------------------------------------------------------------------+
|  INPUT DATASET (1 Record):                                                  |
|  +---------+------------------+------------------------------------------+  |
|  | User_ID | Department       | Skills                                   |  |
|  +---------+------------------+------------------------------------------+  |
|  | 101     | Analytics        | Alteryx,SQL,Python,Tableau               |  |
|  +---------+------------------+------------------------------------------+  |
|                                                                             |
|  TEXT TO COLUMNS CONFIGURATION:                                             |
|  - Target Column: Skills | Delimiter: ',' | Mode: (*) Split to rows         |
|                                                                             |
|  OUTPUT DATASET (4 Records):                                                |
|  +---------+------------------+------------------------------------------+  |
|  | User_ID | Department       | Skills                                   |  |
|  +---------+------------------+------------------------------------------+  |
|  | 101     | Analytics        | Alteryx                                  |  |
|  | 101     | Analytics        | SQL                                      |  |
|  | 101     | Analytics        | Python                                   |  |
|  | 101     | Analytics        | Tableau                                  |  |
|  +---------+------------------+------------------------------------------+  |
+-----------------------------------------------------------------------------+

Critical Mechanics of Split to Rows

1. In-Place Field Replacement: Unlike Split to Columns (which generates new columns with root prefixes), Split to Rows modifies the target column in place, replacing the full delimited string with individual token values.
2. Preservation of Non-Target Fields: All other columns in the record (User_ID, Department, etc.) are duplicated identically across every newly generated row.
3. Record Count Multiplier: If an input record contains $k$ delimiters, it produces $k + 1$ output records. If a record contains no delimiters, it passes through as exactly 1 record.
4. Handling Empty Delimiters: If the input string contains adjacent delimiters (e.g., Alteryx,,Python), the tool creates an empty string row for the missing token.

---

Delimiter Specifications & Special Escape Characters

The Delimiters configuration box accepts single characters, multiple characters, and escaped whitespace sequences.

+-----------------------------------------------------------------------------+
|                        DELIMITER SYNTAX RULES                               |
+-----------------------------------------------------------------------------+
|  Input Entry      Evaluation Logic                                          |
|  -----------      --------------------------------------------------------  |
|  ,                Splits on every comma.                                    |
|  |                Splits on every pipe symbol.                              |
|  \t               Splits on tab characters.                                 |
|  \n               Splits on newline / linefeed characters.                  |
|  \s               Splits on whitespace characters (spaces).                 |
|  \0               Splits on null characters.                                |
|  ,;|              Splits on comma OR semicolon OR pipe (individual chars).  |
+-----------------------------------------------------------------------------+

The "Multiple Delimiters" Rule

One of the most frequently failed questions on the Alteryx Core exam involves entering multiple characters into the Delimiters box.

CRITICAL EXAM RULE: Entering multiple characters into the Delimiters box (such as ,-/) does NOT search for the contiguous substring ",-/". Instead, Alteryx treats each character independently as an individual delimiter (OR logic).

If you specify Delimiters: ,| on the string "Apples,Oranges|Bananas", the tool splits at both the comma and the pipe, yielding three distinct tokens: "Apples", "Oranges", and "Bananas".

---

Step-by-Step Configuration Workflow

To parse a customer contact string into separate components:

1. Drag a Text to Columns tool from the Parse category onto the canvas and connect it downstream of your data source.
2. In the Configuration Window, select the target column from the Select Column to split drop-down (e.g., CustomerContact).
3. In the Delimiters field, specify the separator (e.g., - or \s).
4. Select the parsing direction:
   • Choose Split to columns, set Number of columns (e.g., 3), verify the Extra Characters setting, and enter an Output root name (e.g., Contact_Segment).
   • Or choose Split to rows to normalize values vertically.
5. Run the workflow (Ctrl + R) and inspect the Results Window to verify output columns, record counts, and any logged warnings.

---

High-Yield Exam Traps & Best Practices

• String Data Type Retention: The Text to Columns tool always outputs newly created columns as string data types (V_WString or inherited String). Even if the parsed tokens are numbers (e.g., "100", "250"), they remain strings until converted using a Select or Formula tool.
• Leading and Trailing Whitespace: The Text to Columns tool splits strictly on the delimiter character. If data is formatted as "Dallas, TX, 75001" and split on ,, the resulting tokens will retain leading spaces: "Dallas", " TX", " 75001". Use the Data Cleansing tool or Trim() downstream.
• Split to Rows Ignores Column Settings: When Split to rows is selected, the "Number of columns", "Extra Characters", and "Output root name" settings are disabled and ignored.
• Empty / Null Input Strings: If the target field contains Null, Split to Columns outputs Null across all generated columns; Split to Rows outputs a single record with Null.