/

Split Content of Cell into Multiple Columns in LibreOffice Calc

3445 views

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 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.

Source Data
Source Data

Select the entire column which contains the data. From menu click Data > Text to Columns.

Text to Columns in Menu
Text to Columns in Menu

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.

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.

=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))
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 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)

And the below extract the second part of the string.

=MID(B3,FIND(“,”,B3)+1,LEN(B3)-FIND(“,”,B3))

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.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.

Related


arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials and no BS. Follow us using social channels or send us an email.

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x