Split Cells into Rows

A survey response may contain several selected options in one field. A product may have several tags, or an order may list several item codes separated by semicolons. Split Cells into Rows makes one output record for each item in a selected cell. The identifier and every other field are copied into each resulting record. This creates a detail table that can be filtered by individual tags while retaining the connection to the original response or product.

The operation expands exactly one selected column. It does not combine every item in one column with every item in a second column. That kind of Cartesian expansion can create misleading relationships and is outside this tool's scope. If two fields contain parallel lists, first establish how their items correspond. Running two independent row expansions may multiply the record count and should not be treated as evidence that the resulting pairs were present in the source.

Use Choose files, drag a file onto the input area, or select Paste a table. Confirm file parsing lets you review how the file itself becomes records and columns. Select Parse and preview, verify the header and quoted fields, then Confirm import. CSV, TSV, and UTF-8 delimited text are supported. A line break inside a correctly quoted field remains inside one logical record even if the source occupies more than one physical text line.

Choose Source column and an item delimiter. The item delimiter can be a semicolon, comma, pipe, tab, newline, or another literal string. It is independent of the delimiter used to parse the file. If a CSV has comma-separated columns but the tags field contains red;blue, choose semicolon for the row expansion. If a field contains commas that belong to item names, those commas cannot simultaneously serve as unambiguous item boundaries without an agreed convention in the source.

The defaults preserve original item text, keep repeated items, and keep empty items. These defaults make the initial result easy to compare with the source. Enable Trim item edges only when surrounding spaces are presentation noise. Enable Remove repeated items within a cell only when repeats are not meaningful. Deduplication is local to each source cell: equal tags on two different products remain two different observations. Item order follows the original cell, including when repeated items are removed.

An entirely empty source cell creates one empty item by default. This retains a business record even when its tag list is blank. Consecutive delimiters also produce explicit empty items. With a semicolon delimiter, A;;B contains three items: A, an empty string, and B. Keeping those empty items yields three output records. Turning off Keep empty items yields two records and increments the excluded-item count by one.

A source record disappears completely only if no item survives its configured trimming, empty handling, and duplicate rules. For an empty source cell with Keep empty items disabled, one item is excluded and one source record is fully excluded. For A;;B under the same configuration, one item is excluded but the source record still contributes two outputs. The two counters answer different questions. Check both before concluding that a reduction in output count is harmless.

Empty definition provides separate switches for missing cells, empty strings, and whitespace-only content, plus exact additional markers. By default, an ordinary space is an item, and values such as 0, false, NULL, and N/A are text. Trimming item edges can turn a whitespace item into an empty string, after which the empty-item policy applies. This order is intentional and makes the effect of enabling both settings reproducible.

Suppose the table contains an id column and a tags column. Record 001 has 红;蓝 and record 002 has an empty tags value. Select tags and use a semicolon. With the default settings, the result has three rows: 001 with 红, 001 with 蓝, and 002 with an empty item. The identifier remains text, so its leading zeros survive. The original ordering is retained: all items from the first record appear before items from the second.

Now disable Keep empty items and run again. Only the two rows for 001 remain. The summary reports one empty item, one excluded item, and one fully excluded source record. Excluded records contains the original record for 002, making the disappearance reviewable. Source mapping contains one entry for each surviving output and connects its generated identity to the source record and original item number. Item numbers are not renumbered merely because an earlier item was excluded.

For a different source containing A;A;B, repeated items are kept initially. With local deduplication enabled, the first A and B survive in their original order. With A; A, the two items are distinct until you also enable trimming. These examples explain why trimming and deduplication are separate controls rather than a combined cleanup switch: their interaction changes the meaning of the output and deserves an explicit choice.

Preview sample and estimate calculates an expected output count and displays a small sample. The sample itself is limited to twenty visible output records and cannot be used to infer every source pattern. A full expansion still requires Run and review. The worker checks output records, columns, cells, and the source-mapping report against current limits before building the result. It blocks excessive expansion instead of returning a quietly truncated table.

A small input file can expand substantially. A thousand records with a hundred items each already create a hundred thousand detail records before additional fields are considered. Long copied descriptions can also multiply memory use. Reduce the input, shorten an item list through an explicit prior operation, or divide the work when a limit is reported. Cancel task terminates a long-running worker. No partly expanded table is promoted to a successful full result after cancellation.

Every unselected field is copied as text. If an order total is 100 and the order has three tags, each tag row carries 100. Adding that field across the expanded rows would produce 300, but the expansion did not create two new orders or two additional amounts. Use a business rule that accounts for the original record identity when analyzing such measures. The preview warning is a reminder to distinguish repeated context from a measure that belongs independently to each item.

Source mapping preserves the source identity, file and logical-record location, selected column, and original item ordinal. You may also add Source record and Item number columns directly to the main output using their checkboxes. These optional columns are convenient when a downstream system cannot read a separate mapping report. Their names must not conflict with existing fields. The grid's source location remains available even when those additional exported columns are disabled.

After a full run, choose Export new copy and explicitly select the complete result, excluded sources, or source mapping. A ZIP contains the result and its reports, with a manifest and safe filenames. CSV and TSV exports go through serialization and read-back checks, including quotes, Unicode, and multiline cells. Formula-prefix protection is selectable and may alter text; inspect the affected count before confirming download. It does not remove the need to choose appropriate text import settings in the receiving application.

Apply to workflow makes the expanded table available to grouped filling or filtering. A useful next step is to fill a department within a customer group while keeping original logical-source locations. Save rules if you need to repeat the same item policy, and verify the next file's field structure when loading them. Changing a delimiter or exclusion rule marks the previous result stale. Switching tools or interface language keeps the current session; refreshing or closing clears it, so retain any needed output before leaving.