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.
Table of Contents
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.
- 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.
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.
- After the split, you can see the data is split into two 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.
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.
=LEFT(B2,FIND(",",B2)-1)
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.
=RIGHT(B2,LEN(B2)-FIND(",",B2))
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.
=MID(B3,1,FIND(",",B3)-1)
The below formula extract the second part of the string.
=MID(B3,FIND(",",B3)+1,LEN(B3)-FIND(",",B3))
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.