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
1John 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])

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:

ABCDEFG
1JohnDoejohn@example.com100ExampleStNY

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:

ABCD
1John Doejohn@example.com100 Example StreetNY

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'.
ABCD
1TextNameOccupation 1Occupation 2
2Jim,Salesman
3Dwight,Salesman/Farmer
4
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.