Split an Existing Text Column in Calligra Sheets

Split records that are already stored in worksheet cells, while retaining the original strings and leading-zero identifiers. The split writes into the selected column and adjacent columns, so reserve the destination before opening the dialog.

Scope and preparation

Calligra Sheets 26.08.2 desktop source, English interface, with KDE handbook cross-checks; instructions and expected results are documentation-backed and have not been executed in the native application. Linux-oriented file recovery; Windows, macOS and distribution-specific builds require separate verification.

The 26.08.2 action uses the first selected column’s displayed text and expands rightward from that column. It does not provide a separate destination picker. Use literal text for this exercise, not formulas whose display might differ from their input. No native run or screenshot is claimed.

Enter the original strings

Start a blank workbook. Enter A1=Original record. Enter each line below into the indicated cell as one literal text value, preferably through the input line. Do not paste the lines through a CSV importer or allow them to split during initial entry.

Cell	Exact literal text
A2	0017;North;12
A3	0042;South;8
A4	0105;"West;Annex";5
A5	0009;;7

The quotation marks in A4 are straight ASCII double quotes and are part of the source text. They protect the semicolon inside the location. A5 has an empty middle field, represented by two adjacent semicolons.

Enter G1=Guard, G2=KEEP, B7=Quantity total and E7==SUM(E2:E5). E7 initially displays 0. Leave B2:B5 empty as a spacer. Reserve C2:E5 entirely for the split and ensure F2:F5 are empty too. G2 is a guard outside the destination.

Save split-original.ods with File → Save As…, choosing OpenDocument SpreadSheet. Save a second working copy as split-working.ods.

Copy to the intended destination

Copy A2:A5 with Edit → Copy, select C2 and use Edit → Paste. Verify that C2:C5 still contains four complete strings, identical to A2:A5. Enter C1=ID, D1=Location, E1=Quantity.

Select C2:C5. Choose Data → Text to Columns…. Because the action overwrites the selected text with its split result, never run it on column A in this preservation exercise. Selecting a wider rectangle does not make the extra columns additional independent source columns.

Set the parser explicitly

In the Text to Columns dialog:

1. On Format, choose Semicolon as the delimiter. Do not rely on a delimiter retained from an earlier operation.
2. Set Textquote to the straight double quote ("). Leave Ignore duplicate delimiters unchecked so A5’s missing location remains an empty field instead of shifting its quantity left.
3. Inspect the preview. It must have four rows and three columns. Row 3’s middle field must be West;Annex, and row 4’s middle field must be empty. If not, cancel and fix the literal source or parser settings before changing the sheet.
4. Select a preview cell in the first column and choose Text in the Format list. Verify that the first preview column’s header changes from Generic to Text. Do the same for the second column. Select a cell in the third preview column and choose Generic. These per-column settings preserve identifiers as text and allow integer quantities to become numbers.
5. Confirm that no row or column subset is excluding part of the four-by-three preview, then accept with OK.

The dialog stores settings between uses. Always recheck delimiter, quote, duplicate-delimiter handling and each column’s type when repeating a split.

Check the resulting cells

Row	C: ID, text	D: Location, text	E: Quantity, number
2	0017	North	12
3	0042	South	8
4	0105	West;Annex	5
5	0009	empty	7

E7 should now be 32. Inspect the input for C2 and C4 to ensure the leading zeros survived; visual number formatting alone is not proof that identifiers remained text. A2:A5 must still contain the exact original strings, including the quote characters and adjacent semicolons. G2 must remain KEEP.

Save the intended result as split-final.ods, close and reopen it, and check the same values, text identifiers and E7 formula. Opening a CSV file is a different workflow; this operation started with cells already present in a workbook.

Recover from a wrong split

If you notice the mistake before saving or closing, Edit → Undo can reverse the split; inspect C2:C5 to verify that full source strings returned. For a reliable reset regardless of session history, open the unchanged split-original.ods and save a new working copy. Recreate the headers and copy A2:A5 to C2:C5, then repeat with the corrected parser settings. Do not undo after reopening and expect the prior session’s split to be available.

If zeros were lost, restoring a number format cannot prove which digits were originally present. Restore the preserved source strings and split again with the ID column set to Text. If the third row spills into four columns, check the text quote setting. If row 5’s quantity appears in D5, turn off duplicate-delimiter collapsing and restart from the original strings.

Add a new record safely

Keep split-final.ods unchanged. Open it and save as split-growth.ods. Enter A6=0020;East;9, copy A6 to C6, and confirm D6:E6 are empty. Select C6 only and run Data → Text to Columns… with the same explicit settings and three-column preview. Expect C6=0020, D6=East, E6=9.

Expand E7 to =SUM(E2:E6); the total becomes 41. Save and reopen the growth copy. Existing rows, original strings and G2 must remain intact. A formula that still ends at E5 would silently keep the old total of 32, so growing the split output and growing its downstream formula are separate checks.

For an advanced input contract, add new edge cases only in a separate copy: quoted delimiters are supported by this parser, but embedded line breaks, mixed delimiters and locale-sensitive decimals need their own preview and validation. Do not infer parity with another spreadsheet’s CSV parser. See CSV delimiter and encoding import for the file-import starting point and number and identifier formatting for preserving identifiers.

Sources and verification boundary

The KDE Data menu reference documents Text to Columns. The official Calligra 26.08.2 source archive, sheets/ui/actions/CSVActions.cpp, libs/widgets/KoCsvImportDialog.cpp and .ui, confirms first-column input, expansion from the selection and per-column Text/Generic controls. The expected parsing and totals are independently calculated, not observed native output; installed-build preview, persistence and screenshots remain pending.