LibreOffice Calc · Data cleanup · Beginner
Split Text into Columns in LibreOffice Calc
Split names, IDs and delimited values into columns using LibreOffice Calc Text to Columns, without overwriting important data.
Updated 10 October 2026 · Formula checks: testing standards
When one cell contains several pieces of information, Data → Text to Columns can separate them into individual Calc columns. It is useful for delimited exports, copied contact details and simple product codes. The important precaution is to protect any data to the right before splitting.
Prepare a safe test
In an empty sheet, enter these three lines into cells A2:A4:
Mina|Support|Toronto
Alex|Sales|Ottawa
Sam|Support|Halifax
Your goal is to put the name into column A, department into B, and city into C. Make sure B2:C4 are empty, or use a copy of the original sheet. The operation may replace cells in adjacent columns.
Split the values
- Select
A2:A4. - Choose Data → Text to Columns.
- Select Separated by, then enter
|in Other. Disable separators that do not appear in the data. - Confirm that the preview displays three columns with no accidental extra splits.
- Click OK.
The first output row should look like this:
| A2 | B2 | C2 |
|---|---|---|
| Mina | Support | Toronto |
Preserve identifiers
If a split field looks like 00042, select that preview column and mark it as Text to avoid losing leading zeros. The same advice applies to product versions resembling dates.
When Text to Columns is not enough
It also supports fixed-width splitting, useful when positions rather than separators define fields. But it is not a general parser for irregular, nested records. When the format varies by row, it may be better to first clean the source or use REGEX for a specific extraction rule.
Try the CSV separator guide when the data has not yet been imported into Calc. Text to Columns is most useful when the text is already inside a worksheet.
Common questions
Will the source column remain unchanged? No. Column A becomes the first resulting field. Duplicate the source data if you need an untouched reference.
Can I split using spaces? Yes, but only if a space reliably separates fields. A person’s full name may contain multiple spaces or multiple words, so always check the preview.
Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.