.png)
How to Split Text in Google Sheets (Best Method in 2023)
In this article we will show you how to split text in google sheets in different scenarios. We will show you how to use the split function and then how to split text to both columns and rows.
How to Split Text in Google Sheets
We can use the SPLIT function to split text in google sheets..
Syntax
=SPLIT(cell, delimiter)
Where
Cell: the reference to the cell containing the string to split
Delimiter: also called separators, the special character in the data to use as markers for splitting it to columns; they are closed in double quotes
Note that this formula applies to only a single cell.
1. Identify cell and delimiter
In this example, we identify the following inputs:
Cell: A2
Delimiter “ ” (space)
2. Insert formula in the form =SPLIT(cell, delimiter)
The formula becomes
=SPLIT(A2,” ”)
And we insert this to cell B2.
3. Press Enter.
The output becomes:
You can learn more about this method here.
Split Text to Rows in Google Sheets
We will use the SPLIT and TRANSPOSE functions to achieve this.
Syntax
=TRANSPOSE(SPLIT(cell, delimiter))
Where
Cell: the reference to the cell containing the string to split
Delimiter: the special character in the data to use as markers for splitting it to columns; they are closed in double quotes
Note that this formula applies to only a single cell.
1. Identify the cell to be split and the delimiter
In this example, we identify the following inputs:
Cell: A1
Delimiter “,”
2. Use formula =TRANSPOSE(SPLIT(cell, delimiter))
The formula becomes
=TRANSPOSE(SPLIT(A1,”,”))
And we insert it in cell D1.
3. Press Enter.
The output becomes:
Check this guide for more details.
Split Text to Columns in Google Sheets
1. Select the text to split
The range should be a single column with several rows of entries. The adjacent columns should be blank.
2. Click Data then select the Split text option
The Data option is in the main menu at the top of the page. The Split text to columns option is near the end of the dropdown list.
3. Select the separator
Separators are special characters that mark where you can separate the string to its parts.
A small box where you can select the separator will appear. You can either choose Detect automatically or select it from the drop-down list.
The list includes commas, periods, semicolons, and spaces. We will select Comma for this example.
After selecting the separator, just click anywhere in the sheet to keep the changes.
