In this article:

How to Split Text in Google Sheets (Best Method in 2024)

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

Split text google sheets, select text

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. 

Split text google sheets, enter formula to cell

3. Press Enter.

The output becomes:

Split text google sheets, output displayed

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

Split text google sheets, select text

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.

Split text google sheets, enter formula to cell

3. Press Enter.

The output becomes:

Split text google sheets, output by formula shown

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.

Split text in google sheets, select text to split

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.

Split text in google sheets, click data, select split text option

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.

Split text in google sheets, select comma as separator

After selecting the separator, just click anywhere in the sheet to keep the changes.

Split text in google sheets, text split output

Click here to lean more about splitting text to columns.

If you enjoyed this article, you might also like our article on how to split columns in Google Sheets or our article on how to split text to rows in Google Sheets. 

If you want to learn how to send mass emails from Google Sheets, we also suggest checking out our detailed guide. 

Use our loan portfolio deadline software to easily track deadlines from your spreadsheet in just a few clicks. 

Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started