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 command, a hyphen, etc.
Look at the following data set which contains names in Cell B. The first name and last name are separated by a comma. Using the Text to Column feature you can split the first name and last name into different columns.
Select the entire column which contains the data. From menu click
Data > Text to Columns.
In the next window, Select Separated By as Comma and Press OK. You can also see the LIVE preview of your data after 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.
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.
Method 3 – via Substring MID Function
Using the MID function also you can achieve the same result. Here’s the syntax of 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.
And the below extract the second part of the string.
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.