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:

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 takes in the original text and the text you want to 'split' around, and then divides the original text into multiple cells.


=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:


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:


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:


Here’s the output:

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.
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'.
1TextNameOccupation 1Occupation 2
Next Lesson
hand pointing emoji hand pointing emoji


Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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