Split Column

Cut one column into several columns at a character you choose.

What it does

Split Column cuts one column into several columns. Each piece goes into a new column of its own. By default the original column stays in the table, and you can turn that off.

This is the columns-across version. If you want one row per value instead of one column per value, that is Unnest.

Split a column

  1. Type split in the Search transforms box, in the Pipeline panel.
  2. Click Split Column. The panel opens below the grid.
  3. Click the box under Select column. Pick the column to cut.
  4. Pick how to cut it under Split by. A delimiter is the default.
  5. Type the separator in Delimiter. A space, a comma and a hyphen are all valid.
  6. Type how many pieces you want in Number of parts.
  7. Click Apply.

The picker takes one column only. A new pick replaces the old one. Leave Number of parts empty and you get two columns.

Three ways to cut

  • A delimiter is a piece of text that sits between the parts: a comma, a pipe, a space, the string - . It is matched exactly, not as a pattern, so a dot means a dot.
  • Fixed widths cut at character positions instead. Type the widths in order, separated by commas: 3,4,2 gives three columns of 3, 4 and 2 characters. The widths are also the part count, so there is no separate number to fill in. This is the one to reach for with product codes and fixed-format exports.
  • A regular expression matches the text between the parts, which is what you need when the separator varies: \s*[;,]\s* splits on a comma or a semicolon with any spacing around it.

Text past the last part

Splitting a|b|c into two parts leaves c with nowhere to go. Text past the last part decides what happens to it:

  • Leave it out gives a and b. The tail is dropped.
  • Keep it in the last part gives a and b|c. This is what a spreadsheet's Text to Columns does with its final box, and it is the right answer for something like city, state, full address where only the first field is fixed.

The option is not offered for a regular expression split. The text between the parts is whatever the pattern matched, so there is no way to put the tail back together as it was.

Keep the original column

Keep the original column is on. Turn it off and the source column is dropped in the same step, which saves adding a Delete Columns step straight after.

How the pieces are cut

Part 1 is the text before the first separator. Part 2 is the text between the first and the second separator, and so on. This table shows an event registrations file. The column is attendee_name, the delimiter is a space, and the number of parts is 3.

attendee_nameattendee_name_1attendee_name_2attendee_name_3
Ada LovelaceAdaLovelace
Grace Brewster HopperGraceBrewsterHopper

A row can hold fewer pieces than you asked for. The extra columns are empty for that row.

Names of the new columns

  • The names follow the source column: attendee_name_1, attendee_name_2, and so on.
  • Split the same column twice and the names would clash. The step adds a suffix instead.
  • If attendee_name_1 is taken, the new column becomes attendee_name_1_2.
  • The count goes up until a free name is found. No step fails and no column is written over.

Before you apply

  • The delimiter is required in delimiter mode. An empty box shows an error.
  • The delimiter is matched exactly. A comma with a space after it is not the same as a comma on its own.
  • Widths must be whole numbers of 1 or more, and there must be at least two of them.
  • Ask for one more part than you expect. It is easy to delete a column that stays empty.
  • A piece a row never had comes back empty rather than as an empty string, so Fill Missing and a blank filter both find it.

For SQL users

A delimiter split is one SPLIT_PART call per part, with a fixed position:

SELECT *, NULLIF(SPLIT_PART("attendee_name", ' ', 1), '') AS "attendee_name_1", NULLIF(SPLIT_PART("attendee_name", ' ', 2), '') AS "attendee_name_2" FROM "registrations"

Keeping the tail in the last part joins the rest of the list back up:

ARRAY_TO_STRING(STRING_SPLIT("attendee_name", ' ')[2:], ' ') AS "attendee_name_2"

Fixed widths are SUBSTRING with a running start position, and a pattern split indexes the array a regular expression produces:

SUBSTRING("code", 1, 3) AS "code_1", SUBSTRING("code", 4, 4) AS "code_2"
REGEXP_SPLIT_TO_ARRAY("raw", '\s*[;,]\s*')[1] AS "raw_1"
Try Split Column with sample data →

Related Operations