LibreOffice Calc Text to Columns: How to Split Data

To split text into separate columns in LibreOffice Calc, select the cells, choose Data > Text to Columns, set a delimiter or choose fixed-width breaks, review the preview, and click OK. The command can separate items such as names, comma-delimited values, or codes while keeping the results in the same sheet.

What Text to Columns does

Text to Columns takes the contents of selected cells and divides them across neighboring columns. For example, a cell containing Riley Chen can become two cells, one with Riley and one with Chen. A cell containing North,West,Central can be divided at each comma into three cells.

It is a data-splitting tool, not a way to create a new worksheet or automatically detect every kind of structure. You tell Calc where to split by choosing a delimiter, such as a comma or tab, or by placing breaks at specific character positions. The original text is replaced in the selected cell by the first part, with later parts placed in columns to its right.

This is useful when data has arrived as one long field after copying it from a website, text file, or another application. It can also help clean up imported lists. If a file is still being imported, however, it may be better to set separators and data types in the import dialog rather than splitting the data afterward.

How to use Text to Columns

Save a copy of the workbook or duplicate the source column if the original values need to be preserved.
Select the cells you want to split. Usually, select the populated cells in one column rather than the entire worksheet.
Choose Data > Text to Columns from the menu bar. Calc opens a dialog with splitting options and a preview.
Choose Separated by for delimiter-based data, or Fixed width for data arranged in consistent character positions.
Set the delimiter or break positions, then inspect the preview. Check that each sample value lands in the intended column.
Review any available column type settings, especially for identifiers, dates, and values with leading zeros.
Click OK to apply the split. Check the resulting columns before sorting, calculating, or saving the data.

Keep the columns to the right of the selected data clear before applying the command. A split may need several neighboring columns, and existing information there could be displaced, replaced, or prompt you to confirm an overwrite. For a cautious workflow, copy the source values to a spare area first.

Choose a delimiter or fixed width

Use Separated by when a consistent character marks the boundary between fields. Common choices include tabs, commas, semicolons, and spaces. Choose Other and enter a character when the separator is something less common, such as a vertical bar in item|quantity|price. The preview is the best way to confirm that the selected character is splitting the data as intended.

A delimiter should be chosen based on the data, not simply because it is familiar. If the values themselves contain commas, splitting at every comma can break an address or description into unintended pieces. A semicolon or tab may be a better separator if it does not appear inside the fields. Some dialog versions provide a text-delimiter setting for quoted fields; for CSV-style data, check that quoted text is handled appropriately and verify the preview.

Use Fixed width when fields occupy consistent positions rather than being separated by a character. An old-fashioned report might place an account code in characters 1–8, a date in characters 10–19, and an amount farther along the line. In the preview, add or adjust break markers at the boundaries between fields. Fixed-width splitting is less suitable when field lengths vary, because a break at one position will not follow a short or long value intelligently.

Repeated separators need special attention. A line such as Lee,,Seattle contains an empty field between the two commas. Depending on the separator options selected, consecutive delimiters may be treated as separate boundaries or grouped. Decide whether that empty field represents meaningful missing data, then use the preview to confirm that the city stays in the correct output column.

Examples of common splits

Comma-separated values: Apples,12,Fresh can become Apples, 12, and Fresh in three columns when comma is the separator.
Tab-separated text: Data copied from a table may already use tabs. Choose Tab rather than Space so that multiword entries, such as Customer Service, remain together.
Full names: Splitting Riley Chen at a space produces two fields if each name has one word. Names such as Riley van Chen show why a space is not always a reliable first-name/last-name rule.
Pipe-delimited records: For SKU-18|Blue mug|24, choose Other and enter the pipe character. A hyphen in the SKU will not be treated as a separator.
Fixed-position codes: A string such as AB20261104X could be divided by position if its structure is always two letters, eight date digits, and one final code character.

These examples illustrate an important distinction: the command separates characters or positions, not meanings. It cannot reliably infer that the last word is a surname, that a string of digits is a date, or that punctuation should stay with a particular field. The data layout and the preview determine the result.

Prepare the data and protect important values

Before splitting a large range, inspect several rows for exceptions. Look for missing separators, extra spaces, quoted text, or values with more fields than the others. One irregular row can shift pieces into unexpected columns or leave blank cells. If the data comes from a file, keeping an untouched copy makes it easier to compare the result with the source.

Pay particular attention to codes and account-like identifiers. A value such as 00427 may be converted to the number 427 if Calc interprets the result as numeric, losing its leading zeros. If the split dialog lets you set a column’s type, choose a text type for identifiers that must remain exactly as written. A value that looks like a date can also be interpreted according to the spreadsheet’s locale, so check date order and display after splitting.

Blank cells in the source are not necessarily errors. In a delimited record, two separators in a row can mark an empty field, while a trailing separator can indicate an empty final field. Decide whether those blanks should remain as separate columns. Removing or merging empty fields without checking can shift later values and make a clean-looking table inaccurate.

Numbers, dates, and text formatting

Splitting a field and preserving its exact appearance are separate tasks. Calc may recognize a resulting value as a number, date, or other special value instead of retaining it as plain text. That is useful for quantities you intend to add, but undesirable for product codes, postal codes, and other labels where every character matters.

Use the preview and any column-type controls in the dialog to check how each output field will be interpreted. Set identifiers to text when available. For actual dates, confirm that the displayed date matches the order used in the source, such as day-month-year or month-day-year. If the preview does not make the interpretation clear, test a few representative rows in a copy of the data before processing the full list.

Formatting a cell to display leading zeros is not always the same as preserving them in the underlying value. If the original code must be retained character for character, importing or splitting it as text is generally safer than relying on a number format. Likewise, a number displayed with a currency symbol may still be a numeric value; use the appropriate data type if you need to calculate with it.

Common problems and how to fix them

Everything stays in one column: Confirm that you chose the correct delimiter and that its checkbox or custom-character field is enabled. Inspect the preview before applying the split.
Words split into too many columns: You may have chosen Space when the values contain multiword text. Try a more specific delimiter, such as a tab, comma, or pipe.
Values shift into the wrong columns: Look for rows with missing or repeated separators. Decide how empty fields should be handled and use the preview to check irregular records.
Leading zeros disappear: The output was likely interpreted as a number. Repeat the operation on a copy and set the relevant output column to text if that option is available.
Existing data is affected: The split writes into neighboring columns. Undo immediately if the result is wrong, or restore the saved copy; clearing or moving the destination area beforehand is safer.
Dates look reversed or change format: Check the source date order and the column type or locale interpretation. Do not assume that a plausible-looking date is the correct one.

If the result is wrong, use Edit > Undo promptly, provided you have not performed later actions that you need to keep. For large or valuable datasets, test a small sample first. A successful preview on one ordinary row is not enough if the file also contains long descriptions, blank fields, or quoted delimiters.

When to use import options or another method

If the source is a comma-separated or tab-separated file, opening it through Calc’s file-import process can be a better choice than splitting after the data is already in the sheet. The import dialog lets you inspect separator settings and how columns are interpreted while the file is being brought in. This is especially helpful for CSV files with quoted text, dates, or identifiers with leading zeros. Review the import preview and column types before confirming.

Text to Columns is a direct, menu-based option for a one-time cleanup. For recurring transformations, consider whether the source can be imported consistently or whether a formula-based approach is more appropriate. Formulas can extract parts of text when the pattern is stable, but they require careful handling of exceptions and may need adjustment if the source layout changes. In Calc, function names and argument separators can depend on settings and version, so check the installed version’s function help before building a reusable formula.

For a simple one-off split, the built-in dialog is often easier to review than a formula. For repeated reports, keep the original source unchanged and document the delimiter, data types, and any cleanup steps. That makes it possible to repeat the transformation and spot changes in the source rather than silently producing inconsistent columns.

Frequently Asked Questions

Where is Text to Columns in LibreOffice Calc?
Choose Data > Text to Columns after selecting the cells you want to split. The dialog contains delimiter or fixed-width options and a preview.

Can Calc split text at a space?
Yes. Choose Separated by and enable the Space separator. If some fields contain multiple words, splitting at spaces may create extra columns; use a more specific delimiter when possible.

Can I split text at a character such as a pipe?
Yes. Choose the custom Other separator option and enter the character, then check the preview to ensure it splits only where intended.

How do I keep leading zeros when splitting?
Where the dialog offers a column-type choice, set the relevant output column to text. Work on a copy and verify codes such as 00427 after splitting, because numeric interpretation can remove the zeros.

Does Text to Columns split the original cell or make a copy?
It transforms the selected cells and places the split pieces in columns to their right; it does not automatically preserve a separate copy of the unsplit text. Duplicate the source column first if you need to keep it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.