LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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'.
 A B C D 1 Text Name Occupation 1 Occupation 2 2 Jim,Salesman 3 Dwight,Salesman/Farmer 4
Next Lesson

FREE RESOURCE