|2. Cell Referencing|
|3. Data Types|
|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:
|1||John Doe, firstname.lastname@example.org, 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.
- 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):
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:
|1||John Doeemail@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'.
|1||Text||Name||Occupation 1||Occupation 2|
Google Sheets Cheat Sheet
Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.
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.