Calc comma-separated lists to one unique column: check the array boundary
By ON Lab · Sources checked November 8, 2026
For consistently comma-space-separated lists in current Calc, combine the input with TEXTJOIN, split it vertically with TEXTSPLIT's row delimiter, then apply UNIQUE to keep distinct entries. Check array entry and output-range size as well as the formula: Calc's help explicitly calls for array formulas for TEXTSPLIT and UNIQUE.
A reader asked on November 4, 2026 for several cells of fruit names to become a single column without repeats, with possible input from other sheets. We checked current LibreOffice 26.8 function and matrix-entry help. This is an official-source walkthrough, not an executed Calc workbook test. A separate local Python string check audited the expected sample members and counts only; it did not evaluate Calc formulas.
Check the version and the actual delimiter
The current help lists TEXTJOIN from LibreOffice 5.2, UNIQUE from 24.8 and TEXTSPLIT from 25.8. The combined approach therefore needs a version with all three, including TEXTSPLIT. Function availability does not establish compatibility with every other spreadsheet application's formula or array behavior.
The sample below uses the exact separator comma followed by one space. That is a data assumption. A comma without a following space is a different delimiter, and a meaningful comma inside a name would need another parsing rule. Do not treat this formula as a general CSV parser.
Use a small, inspectable sample
| Cell | Text |
|---|---|
| A1 | apples, oranges |
| A2 | bananas, apples |
| A3 | apples, kiwis, bananas |
| A4 | kiwis |
| A5 | blueberries, apples |
Enter these as five text cells in a disposable sheet. The expected intermediate list has ten tokens and five distinct members: apples, oranges, bananas, kiwis and blueberries. Inspect those members rather than assuming the reader's illustrative output implies a particular sorting policy.
Stage 1: join the cells
=TEXTJOIN(", ";1;A1:A5)Put this scalar formula in a new empty helper cell such as H1. It joins the selected cells with the same comma-space separator and ignores empty input strings. The current help says ranges are traversed row by row. Check that the joined text retains every intended name before moving on.
Use bounded ranges while debugging. Joining entire columns or several large sheets before inspecting a small example makes missing or accidental data harder to locate.
Stage 2: split into rows rather than columns
=TEXTSPLIT(H1;;", ")The empty second argument leaves the column delimiter unspecified; the third argument is the row delimiter. Keep both argument separators. Putting the comma-space in the second argument requests a different orientation.
For this known ten-token example, select a new empty ten-cell vertical output range such as I1:I10, enter the formula in the input line and finish it as a matrix formula. The generic function help uses Ctrl-Shift-Enter; the Mac-specific matrix-entry help we read shows Shift-Command-Enter. Follow the shortcut for your actual platform. Do not type surrounding array braces manually.
Inspect all ten output cells. If the result is one unsplit phrase, check the actual separator characters and formula arguments. If only part of the expected result is visible, check the array-entry method and output range instead of assuming every repeated or missing entry is a UNIQUE problem.
Stage 3: retain distinct entries
=UNIQUE(I1:I10)For this five-member sample, enter that as an array formula into a new empty five-cell column such as C1:C5. Confirm that each intended member appears once. The default uniqueness option retains distinct values even when they occurred several times. Selecting the option for values that occur only once would answer a different question and remove repeated names entirely.
If ordering matters, define and verify the required ordering separately. This walkthrough's completion criterion is the correct set of distinct names, not a promise to match an arbitrary display order.
Combine only after the boundaries are clear
=UNIQUE(TEXTSPLIT(TEXTJOIN(", ";1;A1:A5);;", "))The nested version follows the same stages. Enter it using the documented array procedure and an appropriate empty output range. Keep the helper version while checking a real dataset; it lets you see whether an error came from input collection, tokenization or deduplication.
This is not a claim of automatic dynamic spill behavior. Calc's matrix guide describes a selected output area that is protected against individual structural modifications. When the input or the number of distinct results changes, review the result range and the documented method for editing the array. Do not overwrite cells next to existing work to make room.
Extend inputs carefully
TEXTJOIN accepts additional string or range arguments, so the input-collection stage can be expanded to other reviewed ranges. Add one range at a time, check the joined text and token count, then reassess the required output area. Verify actual sheet references and any quoting needed for sheet names in your file.
Do not add CLEAN or other text transformations simply because they appeared in a forum formula. Removing control characters or normalizing spaces changes data and can merge names that should remain different. Likewise, consecutive delimiters can create empty tokens unless an explicit empty-token policy is chosen. Inspect those cases before applying the formula broadly.
Completion checks
- Confirm that the installed version supports every required function.
- Verify the actual delimiter and bounded source ranges.
- Inspect the joined text, vertical tokens and distinct output separately.
- Use the appropriate array-entry method and preserve surrounding cells.
- Test duplicates, empty inputs and any names containing delimiter-like characters.
- Reopen the saved workbook and check the actual results in its intended application.
Checked November 8, 2026 against LibreOffice 26.8 help and the dated reader question. The ten-token/five-member reference was audited outside Calc. No native Calc calculation, original workbook, cross-sheet formula execution, automatic spill behavior or cross-application round trip was tested.