Shape · Summarize and reshape

Split to Rows

Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.

Also known as: Text to rows

How it works

Pick the Column to split and the Delimiter, , by default. Each piece becomes its own row in the same column, with every other column copied. Trim whitespace around tokens strips spaces from each piece. A value without the delimiter gives one row, and a null or empty value keeps exactly one row rather than disappearing. For a real list column use Explode; to get columns instead of rows use Split to Columns.

Inputs and outputs

Settings

{
  column: string
  delimiter: string
  trimWhitespace: boolean
}

Example

Products carry a tags cell such as red; green; blue. Split tags on ; with trimming on.

More in Summarize and reshape

• Aggregate — Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.
• Group Aggregate — Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.
• Running Aggregations — Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.
• Pivot — Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.
• Unpivot — Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.