How to Use Text to Columns in LibreOffice Calc

LibreOffice Calc provides a built-in “Text to Columns” tool designed to split delimited or fixed-width text from a single column into multiple distinct columns. This guide explains how to access the Text to Columns feature, configure delimiter settings, format data types for individual columns, and handle common parsing scenarios such as CSV data, custom separators, and fixed-width text streams.

How to Access the Text to Columns Tool

1. Select the range of cells in a single column containing the text you want to split.
2. Navigate to the top menu bar and click Data.
3. Select Text to Columns… from the dropdown menu to open the configuration dialog.

Separator Options (Delimited Text)

The Text to Columns dialog offers several options for parsing data separated by specific characters:

• Separated by: Choose standard delimiters by checking the boxes for Tab, Comma, Semicolon, or Space.
• Other: Check this box and enter any custom character (such as a pipe |, hyphen -, or slash /) to split text based on non-standard delimiters.
• Merge delimiters: Treats consecutive delimiters as a single separator. This is particularly useful when parsing text aligned with multiple spaces.
• Text delimiter: Specifies the character used to enclose text strings (typically double quotes " or single quotes '). Text within these quotes will not be split, even if it contains a delimiter.

Fixed Width Parsing

If your data is not separated by characters but instead relies on consistent character counts:

1. Select the Fixed width option in the dialog.
2. In the Fields preview window at the bottom, click directly on the ruler at the positions where you want to insert column breaks.
3. Click and drag existing break lines to adjust their positions, or drag them outside the ruler area to delete them.

Column Data Type Formatting

Before applying the split, you can specify the data format for each resulting column directly inside the preview window:

1. Click on the column header in the preview table (labeled Standard by default).
2. Choose a format from the Column type dropdown:
   • Standard: Automatically detects numbers, dates, and text.
   • Text: Preserves values exactly as entered, preventing the automatic removal of leading zeros (e.g., ZIP codes or ID numbers).
   • Date (YMD / DMY / MDY): Parses date strings according to the specified format.
   • Hide: Excludes the selected column from being imported into the sheet.

Overwrite Considerations

When you click OK, LibreOffice Calc populates the newly generated columns to the right of the original column. If those adjacent cells already contain data, Calc will prompt you to confirm whether you want to overwrite the existing contents. To prevent accidental data loss, ensure there are enough empty columns to the right before running the tool.