Split Content of Cell into Multiple Columns in LibreOffice Calc


There are many ways you can split cell content into multiple columns in a LibreOffice Calc sheet. Here are some of them.

For heavy spreadsheet users, it is often required to split cell contents into multiple columns for data analysis and refining purposes. And it is one of the basic operations you need to learn for a long-term understanding of spreadsheets. Data always comes incorrectly formatted and requires clean-up for further processing. Here’s how you can split cell contents via three methods.

Split Cell Content

Method 1 – Text to Columns

  • The easiest way is to split the contents into columns using delimiters. Delimiters can be any character or punctuation of symbols. For example, a hyphen, etc.
  • Look at the following data set, which contains names in Cell B. A comma separates the first name and last name. Using the Text to Column feature, you can split the first and last names into different columns.
Source Data
Source Data
  • Select the entire column which contains the data. From menu click Data > Text to Columns. If you are using Tabbed bar, you can find it in the data tab.
Text to Columns in Menu
Text to Columns in Menu
text to column option in LibreOffice tabbed bar (ribbon)
text to column option in LibreOffice tabbed bar (ribbon)

In the next window, Select Separated By as a Comma and Press OK. You can also see the LIVE preview of your data after the split at the bottom of the dialog.

Text to Columns Window
Text to Columns Window
  • After the split, you can see the data is split into two columns.
After Split Cell Content using Text to Columns
After Split Cell Content using Text to Columns

Usage Notes while using Text to Columns

  • Text to columns works from left to right direction. That means the split contents would be filled up to the right-side columns of your source data.
  • And the text to Columns would overwrite the columns in the right direction. So, ensure that you have sufficient empty columns by inserting them.
  • The Text to Column window also provides a fixed-width split if your source data is not delimited by any character. For example, in the same example, you can select Fixed Width and click on the preview bar to create as many split sections as you want.
Fixed width Text to columns
Fixed width Text to columns

Method 2 – via LEFT, RIGHT Function

Using the combination of LEFT, RIGHT, and FIND function, you can also split the cell content. For example, if your data is separated by a comma, then find out the position of the comma in the string then extract the sections.

The FIND function returns the position of the comma.

The LEFT function extracts the left section of the name from position 1 to the before comma’s position. For cell B2, the FIND returns 4.


Getting the second part of the string is tricky. You have to calculate the length of the section from the comma’s position until the end. To do that, you can subtract the comma’s position from the length of the entire string. You can use the RIGHT function.

Using RIGHT and LEFT function
Using RIGHT and LEFT function

Method 3 – via Substring MID Function

Using the MID function also, you can achieve the same result. Here’s the syntax of the MID function.

MID (target string, start position, length) : returns the string of length from start position

As per the above example, the following formula extracts the first part of the string.


The below formula extract the second part of the string.

Using MID function
Using MID function

Closing Notes

I hope these methods help you to split the cell content into multiple sheets for your use case. In case if it’s not working, drop a comment below.

Join our Telegram channel and stay informed on the move.

Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Newest Most Voted
Inline Feedbacks
View all comments

Please when I select the data, text to columns becomes faint.