---
title: "Split a cell into columns or rows | Oplema"
description: "Learn to split a packed cell into columns, explode a list into rows with Split to rows in Strata and total revenue before the split so it isn't doubled."
url: "https://oplema.com/learn/many-sources/split-packed-cells"
updated: "2026-11-04"
---

Lesson 6 of 9 · Lab

# Split packed cells into columns or rows

Split to columns turns one cell into several columns on the same row. Split to rows gives each part its own row and copies everything else, including the money, so a total after the split counts it again.

Both blocks split text on a character you choose, the splitter. Split to columns names the parts `desk_1`, `desk_2` and so on; Split to rows repeats the row.

Marit, who owns [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow), asks which extras sell and when in the day bikes go out, because that's when a shop runs short. The answers sit in `extras` and `desk`, two more columns you dropped in the first course: `helmet;lock`, and `Tomas|09:30|T2` for who served, when and at which till. The stack holds September's 1,096 finished hires, and the bottom **Summarize** totals revenue after the split.

Split to rows turns `helmet;lock` into two rows. What happens to that hire's `amount_eur`?

It's copied onto both. One hire is now two rows, so a sum after the split counts it twice.

## Unpack the cells and keep the money right

1. [Strata](https://oplema.com/strata) is Oplema's browser app for data prep, and this live flow opens on **Split to columns**. Its **Splitter** is a comma, which `desk` doesn't contain, so everything lands in `desk_1`. The **Formula** and **Summarize** beside it are red until you wire them.
2. Set **Splitter** to **Pipe (|)**: `desk_1` is the staff member, `desk_2` the time and `desk_3` the till.
3. Drag from **Split to columns** to the **Formula** beside it, which takes the hour from `desk_2` as text: its **Output type** is VARCHAR. Its **Summarize** shows bikes out by hour.
4. Select **Split to rows**: its **Splitter** is a comma too, so nothing changes yet. The bottom **Summarize** reads 1,096 rows and a `gross` of 50337.5.
5. Set its **Splitter** to **Semicolon (;)**. The bottom **Summarize** now reads 1,336 rows and 61487. The money went up by itself.
6. Keep the extras count after the split, but total revenue where each hire is still one row. Check: 1,096 rows and 50337.5 again. If you're stuck
   Select the line into the bottom **Summarize** and press Delete, then drag from **Union**'s right dot to it. Delete first: a block's input takes one wire, and dragging a second one onto it does nothing.
7. Select the middle **Summarize** for the count of each extra.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the two branches should show

Extras: helmet 479, none 372, child seat 246 and lock 239. Revenue: €50,337.50 with VAT, over 1,096 hires. The busiest hour is 11:00, with 255 bikes out.

## Count after the split, total before it

240 hires have two extras, and their €11,149.50 is exactly what the split added. Count extras on the long table and total money on one row per hire, each from its own branch.

`desk` needed columns because each part means something different; extras needed rows because each part is the same kind of thing.

Your line for Marit: "Helmets go out on 479 hires. Revenue is unchanged at €50,337.50 gross. Most bikes go out at 11:00."

See the [Split to columns](https://oplema.com/docs/strata/parse/split-to-columns) and [Split to rows references](https://oplema.com/docs/strata/parse/split-to-rows). For later: [Join fan-out](https://oplema.com/learn/business-intelligence/joins-integration) shows the same inflation coming from a join.

## Common splitting mistakes

- **Totaling after Split to rows.** Every copied row carries the whole amount.
- **Choosing the wrong splitter.** A splitter that never occurs splits nothing, with no error. Check that `desk_2` exists.
- **Splitting into columns when the count varies.** Rows handle any number of extras.

## Splitting in SQL and Excel

```
SELECT  split_part(desk,  '|' ,  2 )  AS  time  FROM  hires;

SELECT  unnest( string_split (extras,  ';' ))  AS  extra,  count ( * )  AS  hires
FROM  hires  GROUP BY  extra;
```

`unnest` repeats the row like Split to rows, so sum `amount` without it. In Excel, **Text to Columns** splits into columns, and `=TEXTSPLIT(J2, ";")` returns the parts.
