Split Existing Text into Columns in OpenOffice Calc

Text to Columns expands cells already in a worksheet. It can overwrite the cells to their right, so make a separate working copy of the column before choosing delimiters and field types.

Scope and complete raw column

Apache OpenOffice Calc 4.1 desktop, English Windows/Linux menus; documentation checked against the official 4.1 Calc Guide and versioned 4.1.6 Help on 19 October 2026. No native application run or save/reopen test was performed for this exercise.

This guide uses Data → Text to Columns, not File → Open for a CSV. The two workflows share text interpretation concepts but have different starting points.

In a new workbook, enter Raw line in A1. Enter each of the following as one complete cell value through the input line or by typing directly. If pasting opens a Text Import dialog, cancel and enter each line individually so the starting point really is one cell per line.

Cell	Complete literal content
A2	00127;Cable;3
A3	00128;"Clamp; large";2
A4	00129;;5
A5	00130;Case;4

The double quotes around Clamp; large are straight quotation marks. They protect an embedded semicolon. A4 has an intentionally empty middle field between two separators.

Enter ID in D1, Item in E1 and Units in F1. Leave D2:F5 and G2:G5 empty. Put DO NOT CHANGE in H2. Save split-before.ods. Copy A2:A5 to D2:D5 using ordinary Copy/Paste within Calc. Confirm A2:A5 is still intact and every D cell still contains the full raw line.

Configure the split before committing

1. Select D2:D5, the copied column only.
2. Choose Data → Text to Columns.
3. Select Separated by and enable Semicolon only. Disable Tab, Comma, Space and Other. Keep Merge delimiters off, otherwise the deliberate empty Item in the third record could disappear.
4. Set Text delimiter to the straight double quote character: ".
5. In the preview, click the first field’s heading and choose Column type → Text. Select Text for the second field as well. Select Standard for the third field.
6. Where shown, leave Detect special numbers off. All quantities here are integers, so no date or locale-sensitive numeric interpretation is needed.
7. Inspect every preview row. There must be exactly three fields. The second row’s Item must remain Clamp; large, and the third row’s Item must be empty.
8. Confirm only if that preview is correct.

The Text to Columns Help describes expansion in the same row and separator removal. The Text Import field definitions explain Text column type, text delimiters and Merge delimiters. Set the ID type before conversion: changing a number to Text afterward will not reconstruct its missing leading zeros.

If an overwrite warning appears, cancel and inspect D2:F5. The destination should have only the copied raw column and empty neighboring fields. Never accept an unexpected overwrite just because the preview looks right.

Compare all resulting cells

Row	D: ID, stored as text	E: Item	F: Units, numeric
2	00127	Cable	3
3	00128	Clamp; large	2
4	00129	empty	5
5	00130	Case	4

The enclosing quotes are not part of the Item output. The semicolon inside that field remains. G2:G5 must remain blank, H2 must still contain DO NOT CHANGE, and the four raw strings in A2:A5 must be unchanged.

Enter these controls outside both source and result, for example J1:K6:

J: Check	K: Formula	Expected
Exact ID	=EXACT(D2;"00127")	TRUE
ID type	=ISTEXT(D2)	TRUE
ID length	=LEN(D5)	5
Quoted field	=EXACT(E3;"Clamp; large")	TRUE
Numeric count	=COUNT(F2:F5)	4
Units total	=SUM(F2:F5)	14

Here J1 contains Exact ID and K1 its formula, with the next five pairs on successive rows. The last two results are independently derived from 3+2+5+4=14. Inspect E4 visually as an empty cell. A total alone cannot detect a shifted Item/Units field.

For broader identifier handling, see preserve product IDs. Its native CSV and manual-entry tests are separate from this unexecuted Text to Columns exercise.

Change one raw record and rebuild safely

Change A4 to 00129;Sleeve;6. The existing split output does not automatically change: D:F is a converted copy, not a linked query. Before rerunning, clear contents from D2:F5 while leaving D1:F1 headings and H2 alone. Copy the four raw lines from A2:A5 back to D2:D5, select that single column and repeat the settings above.

Now E4 should contain Sleeve, F4 should be 6, and the numeric total should be 15. The other three records stay the same. This repeat is also a useful check against stale outputs.

For an optional fifth record, enter 00131;Tray;1 in A6. Expand the cleared output region to D2:F6 and the copied input to A2:A6. Rerun on D2:D6, then deliberately update COUNT/SUM to F2:F6. Expected count is 5, total 16. Merely typing another raw line does not expand a previous split or its audit formulas.

Troubleshooting and recovery

• One output column: Semicolon was not selected, or Fixed width was active.
• Four fields in the Clamp record: the text delimiter was missing or the quotation marks were curly/unbalanced. Cancel and fix the source copy.
• The empty Item vanished: turn Merge delimiters off, then start again from the untouched raw data.
• 00127 became 127: repeat from the raw strings with the first preview column set to Text. A five-digit number format only changes appearance.
• The right-hand area was overwritten: Undo immediately within the open session, or reopen split-before.ods. Do not attempt to infer lost neighboring values from the split result.
• Numbers are text: inspect the third preview field’s type. Do not change the ID column to Standard to solve a Units problem.

Save the verified result as split-result.ods and reopen it to check raw strings, exact IDs, quoted Item and controls. This persistence check has not been run by the author. For a file-based semicolon/decimal-comma workflow, use the separate CSV import tutorial. No screenshots from that import are presented as evidence of this split.