How to Split Text to Columns or Rows in Google Sheets by Delimiter

0
370
Split Text to Columns or Rows

With the help of SPLIT function, you can split text to columns or rows in Google Sheets. It may be new to Excel users.

We are going to learn Google Sheets Split function here in this tutorial. So get ready. If you are familiar with the Excel Convert Text to Column Wizard, it’s equal to that. Google Sheets Split Function is an alternative to this Excel feature.

As already told above, SPLIT is a function in Google Sheets so it has an edge over Excel’s Text to Column Wizard. Since there is no SPLIT function in Excel, there are of course workaround to achieve the result using combination of text functions. I’m not going to that because our topic is Google Spreadsheets.

Why the Feature Split Text to Columns or Rows Required in a Spreadsheet?

There are lots of reasons behind it. Still let me explain you few scenarios.

Scenario 1:

Suppose you have an employee list. When you have entered the first, middle and last name of the employees together in each cells and want to get it in three different columns.

Scenario 2:

When you have imported content from some other software like Tally, which may normally in a comma delimited format. So it’s must to split the text to make it in a tabular form.

Scenario 3:

When you have a comma delimited SIF file or a SIF file created using Google Sheets for WPS.

Scenario 4:

Any other similar situations when you want to split text strings to columns.

So let us begin with examples. You can straightway go to the example instead of going through the syntax.

Syntax of SPLIT function

SPLIT(text, delimiter, split_by_each)

How to Split Text in a Cell to Columns (Text to Columns)

See the below chart to understand how to use split function with different delimiters in Google Sheets.

How to Split Text to Columns in Google Sheets

In the above example the formula in cell D2 is as below. I will just explain this formula. Rest you can understand from the above chart itself.

=split(A2,” “)

This is to split the text in Cell A2 based on the delimiter “space”. So the result will be two separate words “Info” and “Inspired”.

See also other formulas used in the above chart. You can see “comma” and “semicolon” delimiters used instead of space. If you have learned the above SPLIT function usage move to the below tips.

How to Split Text in a Cell to Rows (Text to Rows)

By default, the split function split the text to different columns. When you want to split the text to rows, use the transpose function in Google Sheets as below.

Split to columns

=split(A2,” “)

Split to rows

=transpose(split(A2,” “))

If you are looking for some advanced split feature in Google Sheets, feel free to check our Regexextract Function tutorial.

Google Sheets Array Formula With SPLIT Function

When you want to deal with same type of text in large number of rows, you can use Array Formula together with SPLIT. By saying same type of text, I meant texts in different cells where you can apply same delimiter. See the example below.

I used the split function to split the names in Column A. Here I have used the array formula together with split function to reduce the number of formulas. Only one formula in Cell B13 can expand the result to adjoining rows and columns.

Split function with Array Formula in Google Sheets

Conclusion:

To learn the use of split text to columns or rows, regular use of this function is a must. You can also bookmark this page to refer later. Thanks for the stay!

LEAVE A REPLY

Please enter your comment!
Please enter your name here