How to split a cell horizontally in Google Sheets

Split Function to Split Text to Columns or Rows in Google Sheets

With the help of the 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 the use of the Split function Google Sheets in detail here. If you are familiar with the ExcelConvert Text to Column Wizard, its equal to that.

Update: Lately Google Sheets has added a menu command for Split called Split text to columns. You can find that under the Data menu in Google Sheets.

As Ive already mentioned above, SPLIT is a function in Google Sheets so it has an edge over Excels Text to Column Wizard.

Since there is no SPLIT function in Excel (at the time of writing this post), there are of course workarounds to achieve the result using a combination of text functions.

Im not going into those details because our topic is different.

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

There are lots of reasons behind it. Here are a few scenarios.

Scenario 1:

Assume you have an employee list. You have entered the first, middle and last names of the employees together (as combined) in each row in a column.

If you want to get it in three different columns, then you can use the Split function in Google Sheets or the above said new Data menu command.

Scenario 2:

Assume you have imported some content from some other software tools like Tally, which may normally in a comma-delimited format.

You may want to split the imported text to make it in a tabular form. Here also you can take the help of the Data > Split text to columns or the Split function in Google Sheets.

Scenario 3:

When you have a comma-delimited SIF file or a SIF file created using Google Sheets for WPS, the Split function will be handy for you to organize (make tabular form) such data in Google Sheets.

Scenario 4:

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

SPLIT function in Google Sheets Syntax and Arguments

Syntax:

Arguments:

text The text to split.

delimiter The character or characters in the text to be used to split.

split_by_each This is optional and TRUE by default. It determines whether or not to split text around each character contained in the delimiter (detailed under the examples).

remove_empty_text This is optional and TRUE by default (detailed under the examples).

Below are some examples to the use of the Split function in Google Sheets.

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

See the below screen capture to understand how to use the Split function with different delimiters in Google Sheets.

In the above example, the formula in cell D2 is as below. I will just explain this formula, the rest of the formulas you can understand from the above examples itself.

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.

In other examples, you can see the use of comma and semicolon as delimiters.

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

By default, the split function will split the text into different columns. When you want to split the text into rows, use the transpose function with SPLIT.

First, see one more example to Split to columns.

Split to Columns

Split to Rows

The same above formula but with Transpose.

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

Google Sheets Array Formula with SPLIT Function

When you want to deal with the same type of split in a large number of rows, you can use Array Formula together with SPLIT.

Ive used the Split function to split the names in column A. Here I have used the array formula together with the Split function to reduce the number of formulas.

Only one formula in cell B13 can expand the result to adjoining rows and columns.

split_by_each The Optional Split Argument

This is useful when your delimiter to split has multiple characters. By default is it set to TRUE. That means if you omit, or include TRUE, the formula will take each character in the delimiter as individual delimiters.

For example;

Cell A1 contains the word INDIA AB BRAZIL (it has no specific meaning, just to make you familiar with the argument).

OR

In this, the characters AB are the individual delimiters to split.

Result:

See the result when using the FALSE Boolean instead of TRUE.

But here is the real catch of the use of the above split_by_each optional argument.

Multiple Delimiters in Split Function in Google Sheets

Can we use multiple delimiters in Google Sheets Split function?

Yes! Set the above split_by_each argument to TRUE or omit. Then use the multiple delimiters within double quotes as below.

This is like OR use of delimiters in Split.

remove_empty_text The Optional and Final Split Argument

This argument is TRUE by default. So you can include or exclude this argument. It removes additional blank cells in the SPLIT result.

The formula in cell B1.

The formula in cell B2.

Conclusion

To learn the advanced use of the Split function to split text to columns or rows in Google Sheets, regular use of it is a must.

Here are some advanced tutorials related to Split.

• Split Number to Digits in Google Sheets.
• How to Use Trim Function With Split in Google Sheets.
• Split to Column and Categorize Google Sheets Formula.
• Split a Column into Multiple N Columns in Google Sheets.
• How to Split Number from Text When No Delimiter Present in Google Sheets.

• TAGS
• Google Doc
• Spreadsheet