Split Columns Transform

Use Split Columns to separate one field into several new columns wherever a literal separator appears.

Basic Usage

To split a column:

1. Select the Split Columns transform from the transform menu.
2. Choose a field under Target Columns.
3. Enter the exact Separator.
4. Enter New Column Names.
5. Optionally, set Max Split and Fill Value.
6. Apply the transformation.

Configuration Options

Basic Options

• Target Columns: Choose a column to split.
• Separator: Enter the exact character or string between values. To split on a space, press the space bar once without quotation marks. Any quotation marks you type become part of the separator.
• New Column Names: Enter the names of the generated columns.
• String Conversion: Values in the selected column are always converted to strings before they are split. This conversion cannot be turned off.

Advanced Options

• Max Split: Maximum number of splits per value. Enter -1 for no limit. (Default: -1)
• Fill Value: Value placed in empty generated cells. (Default: empty string)

Examples

Example 1: Splitting Full Names

Input Dataset:

full_name	email
John Doe	john.doe@example.com
Jane Smith	jane.smith@example.com
Michael Johnson	michael.j@example.com

Configuration:

• Target Columns: full_name
• Separator: a literal space (press the space bar once; do not enter quotes)
• New Column Names: first_name, last_name

Result:

full_name	email	first_name	last_name
John Doe	john.doe@example.com	John	Doe
Jane Smith	jane.smith@example.com	Jane	Smith
Michael Johnson	michael.j@example.com	Michael	Johnson

Example 2: Splitting Addresses

Input Dataset:

address	postal_code
123 Main St, Springfield, IL	62704
456 Elm St, Chicago, IL	60616
789 Oak St, Naperville, IL	60540

Configuration:

• Target Columns: address
• Separator: ,
• New Column Names: street, city, state
• Max Split: 2
• Fill Value: Unknown

Result:

address	postal_code	street	city	state
123 Main St, Springfield, IL	62704	123 Main St	Springfield	IL
456 Elm St, Chicago, IL	60616	456 Elm St	Chicago	IL
789 Oak St, Naperville, IL	60540	789 Oak St	Naperville	IL

tip

Use Fill Value when some rows contain fewer parts than the configured output columns.

caution

Rows can split into different numbers of parts. Preview the result before using the generated columns downstream.

Best Practices

1. Use a separator that appears consistently in the source values.
2. Set Max Split and Fill Value when rows contain different numbers of parts.
3. Give generated columns clear, unique names.
4. Keep the source column when you may need the original value.
5. Preview mixed-type fields because all values are converted to strings first.

Troubleshooting

• Unexpected columns are empty: Check that the separator appears consistently.
• Splitting on a space does nothing: Make sure Separator contains one literal space and no quotation marks.
• Mixed-type values look wrong: String conversion is automatic. Check how the source values are represented as strings.
• Parts are missing: Increase Max Split.