Google Sheets 101: Splitting Text

LESSONS

Text data can be inconvenient.

You want it nicely separated so you can analyze it but instead it comes to you in one cell like this:

	A
1	John Doe, john@example.com, 100 Example Street, NY

If you had a lot of data like this you wouldn’t be able to sort it (except by first name) and you wouldn’t be able to quickly access the data points (e.g. if you needed only the email addresses).

However, the data is separated within this one cell. By a comma and a space.

This is where the SPLIT function comes in.

SPLIT

SPLIT takes in the original text and the text you want to 'split' around, and then divides the original text into multiple cells.

Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

text = the text you want split
delimiter = the text you want the text to split around
[split_by_each] = optional setting as to whether you want the text to split around each character of the delimiter (TRUE - default) or the entire delimiter (FALSE)
[remove_empty_text] = optional setting as to whether empty text from the split (where two delimiters occur consecutively in the original text) is removed in the output (TRUE - default) or included (FALSE)

That’s a lot of options, let’s get stuck into an example:

Example

Let’s use the SPLIT function to split the above text (which is in A1):

=SPLIT(A1,", ",TRUE)

Leaving out the last option is fine as it doesn’t come up that often.

Here’s the output:

	A	B	C	D	E	F	G
1	John	Doe	john@example.com	100	Example	St	NY

This data is completely split apart. Because [split_by_each] was true, the split happens around both the provided comma AND the space.

If you instead wanted the data between each ", " to stay together, you could use:

=SPLIT(A1,", ",FALSE)

Here’s the output:

	A	B	C	D
1	John Doe	john@example.com	100 Example Street	NY

It’s important to note here that SPLIT outputs an array of data across multiple cells.

You have to make sure there’s enough blank cells nearby otherwise you will get a #REF! error that says 'Array result was not expanded because it would overwrite data'.

Splitting Text Exercises

Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.

1. SPLIT

In cell B2, enter a formula that splits the text A2 into separate name and occupation cells.

2. SPLIT By Each

In cell B3, enter a formula that splits the text A3 into three cells for the name and both occupations. Remember you can 'split by each'.

Current question: See the correct formula or clear the formula bar.

	A	B	C	D
1	Text	Name	Occupation 1	Occupation 2
2	Jim,Salesman
3	Dwight,Salesman/Farmer
4

Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.