Split to columns
Separate text such as comma-separated names or pipe-separated codes into columns. Choose the separator and keep each part on the same row, with the option to keep the original text.
Split names into columns
Manual Input has four rows with id and name columns. Split to columns starts with name selected and a comma separator. Ada,Lovelace becomes Ada in name_1 and Lovelace in name_2.
Split to columns interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| id | name | name_1 | name_2 |
|---|---|---|---|
| 1 | Ada,Lovelace | Ada | Lovelace |
| 2 | Lin,Chen | Lin | Chen |
| 3 | Sam | Sam | |
| 4 |
- Clear Keep original column. The
namecolumn disappears from the output, leavingid,name_1andname_2. - Change Splitter to
Pipe (|). None of the names contain a pipe, so the entire name goes intoname_1andname_2disappears. - Choose
Customin Splitter and enter a comma in Pattern. The names split into two columns again. - Use Reset example, then select the Select block and select its output port. It renames
name_1tofirst_nameandname_2tolast_name, keeping the original values.
When to use
Use Split to columns when one cell holds a fixed set of parts, such as last name and first name separated by a comma. If the number of items varies, use Split to rows.
Read the output
With the default settings, the output contains id, name, name_1 and name_2. Ada's row becomes Ada and Lovelace; Lin's becomes Lin and Chen. The original names stay alongside the new columns.
The row containing Sam has no comma. Its name_1 is Sam and name_2 is an empty string. The final row has a NULL name, so both split columns are NULL. An empty string is a text value; NULL means a value is missing.
All four rows stay. Clearing Keep original column only removes name from this block's output; it doesn't change the input table.
The example ends with Select, which gives the numbered columns meaningful names: first_name and last_name. With the original settings, its output contains id, name, first_name and last_name.
How many columns are created?
The value with the most parts sets the number of output columns. They are named after the source column, followed by _1, _2 and so on, and are added after the kept input columns.
Rows with fewer parts get empty strings in the remaining positions. NULL source values produce NULL parts. If no value contains the separator, the block creates just one split column containing the original values.
To put each part on a separate row instead, use Split to rows.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Choose the text column and the literal separator between its parts.
- Column
Choose the text column to split. Only text columns are available; the first is selected automatically.
- Splitter
Choose Comma (,), Pipe (|), Tab, Semicolon (;), Space or Custom. The separator is matched exactly and is removed from the resulting parts. Default: Comma (,).
- Pattern
Shown only for Custom. Enter a nonempty literal separator, such as ::. It can contain multiple characters and isn't a regular expression.
- Keep original column
Keep the unsplit text alongside the new columns, or clear to remove it from the output. Other input columns remain. Default: true.
Split to columns FAQs
- Does splitting add or remove rows?
- No. Every input row stays on one output row. The split parts become additional columns.
- Are spaces trimmed from the parts?
- No. Spaces stay unless they're part of the separator. Splitting Ada, Lovelace on a comma leaves a leading space before Lovelace.
- What happens with consecutive separators?
- They produce empty parts. For example, Ada,,Lovelace creates Ada, an empty string and Lovelace in three numbered columns.
- What if an output column name already exists?
- The block reports an error rather than overwriting it. Rename the existing column before splitting.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.