- Got Sheet
- Posts
- Clean Messy Cells
Clean Messy Cells
Using Split Beyond the Basics
Eamonn Cottrell
October 23, 2025 • Estimated Reading Time: 6 minutes
OVERVIEW
Splitting text in Excel and Sheets
Using the optional arguments
Using formulas as delimiters
Video walkthrough below too
RESOURCES
Good tools to make life easier. You’re welcome :)
Coefficient - Live spreadsheet connections to 60+ business systems
Lido - Automate your spreadsheets; accurately extract date from PDFs
TransactionPro - quickly import, export or delete data in Quickbooks
beehiiv - my choice for a newsletter operating system
Carrd - free one-page website builder
Transistor - my favorite podcast host
Main Tutorial
First, the basics. If you ever need to split text, both Excel and Sheets have built in formulas to do this: TEXTSPLIT() in Excel and SPLIT() in Sheets.
Why they’re named differently is a great question. 🤷
They take a reference and a delimiter for their arguments.
The delimiter simply says what to split the text by. If you use a comma, then every time there’s a comma in the cell, it will split the text there.
If you’re using a character like a comma or a letter or something else as the explicit delimiter, you’ll need to put that in quotation marks in the formula. It will look like this:
=SPLIT(F2,",")
You can see the result below. every piece of text gets put in a new cell in that row and the commas (the delimiter) are removed entirely.
Optional Arguments
By default, [split_by_each] is TRUE. This means that if you were to put both a comma and a space in quotation marks, it will split the text everywhere it sees a comma AND everywhere it sees a space.
In our example, it would split out a lot more text because every word has a space between it.
It can be useful to manually type FALSE for this argument. For instance, if there spaces after the commas (as there typically are unless it’s a csv file), every bit of text that’s split will contain a leading space.
By entering =SPLIT(F2,", ",FALSE) we can split by the commas only and get rid of those spaces too.
By default, [remove_empty_text] is TRUE in Google Sheets…but it’s FALSE by default in Excel 🤯.
This ignores consecutive delimiters. So, if there are two commas in a row, it’s going to ignore the second one and treat that as one delimiter.
If you’re dealing with a csv file this might be problematic because the two commas could represent a data field that is simply empty for that entry. To get around this, set this argument to FALSE.
Using Formulas as Delimiters
You might need to do something fancier that simply commas.
Just this week I had a report at work for employees that had each employee’s contact info in one cell.
Because the program I got it from is dumb, this was the best output for the info I needed.
I had data that looked like this:
Now, I appreciate line breaks in cells as much as the next person (alt+Enter). But, I needed the fields in separate cells.
To accomplish this, I first split by using CHAR(10) as the delimiter. The CHAR() formula returns a unicode character, and the unicode character 10 represents the line feed (which is computer speak for when you hit Return).
This is another instance where your results may differ depending on Sheets vs Excel.
Because there is a blank line (two Returns) in the cell, Google Sheets is going to ignore that by default ([remove_empty_text]).
However, you’ll get blank cells in Excel unless you do one of two things:
set [remove_empty_text] to TRUE, or
use
CHAR(10)&CHAR(10)as the delimiter.
NEXT STEPS
Whenever you’re ready, here’s how I can help:
Work with me
I am available for consulting projects. Reach out and let’s chat.Business tech stack
My recommendations for software and tools I use to create content and run my online business.Personal budget tool
As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials
Reply