The SPLIT function in Google Sheets is used to divide a text string (or value) around a given delimiter, and output the separate pieces into their own cells.
SPLIT Function Examples
Let’s see a simple example using SPLIT to separate a list of names in cell A1:
This simple SPLIT formula will separate these names, using the comma as the separator:
=SPLIT(A1,",")The result is 5 cells, each containing a name. Note that one cell looks blank because the text string in cell A1 has two adjacent commas with a space between them. The “space” is interpreted in the same way as the names and contained in the output:
Now watch what happens if we include a space in the delimiter, i.e. ", "
=SPLIT(A1,", ")The function splits on the comma "," and on the space " ", so the name “Mary Jo” split in two:
This is probably not the desired behavior.
The third argument is an optional TRUE or FALSE that determines whether SPLIT considers each individual character of the delimiter (TRUE) or only the full combination as the separator to use (FALSE).
In our example, adding FALSE ensures that it only considers the combined comma/space string as the delimiter:
=SPLIT(A1,", ", FALSE)And the output looks like this:
There is a fourth argument too, which is optional and takes a TRUE/FALS value. It determines whether to remove blank cells or not in the output.
To illustrate this, consider this arrangement of data separated by semi-colons. Note the presence of two adjacent semi-colons with no data between them:
The fourth argument determines whether to show or hide the blank cell caused by the two adjacent semi-colons.
To keep the blank cells, add FALSE as the fourth argument:
=SPLIT(A2,",", TRUE, FALSE)SPLIT Function in Google Sheets: Syntax
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])It takes 4 arguments:
text
This is the text string or value in the cell that you want to split. It can also be a reference to a cell with a value in, or even the output of a nested formula, provided that output is a string or value and not an array.
delimiter
The character or characters used to split the text. Note that by default, all characters are used in the division. So a delimiter of “the” will split a text string on “the”, “he”,”t”,”h”,”e” etc.
This behavior can be controlled by the next argument:
split_by_each
This argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.
The TRUE behavior splits by individual characters in the delimiter and any combination of them. The FALSE behavior does not consider the characters separately, and only divides on the entire delimiter.
remove_empty_text
The fourth and final argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.
It specifies what to do with empty results in the SPLIT output. For example, suppose you’re splitting a text string with a "," and your string looks like this: “Ben,Bob,,Jenny,Anna”
Between the names Bob and Jenny are two commas with no value between them.
Setting this final argument of the SPLIT function to FALSE results in a blank cell in the output. If this fourth argument is omitted or set to TRUE, then the blank cell is removed and “Bob” and “Jenny” appear in adjacent cells.
SPLIT Function Notes
- Delimiters in SPLIT are case sensitive. So “t” only splits on lower-case t’s in the text
- The SPLIT function requires enough “space” for its output. If it splits a text string into 4 elements then it requires 4 cells (including the one the formula is in) on that row to expand into. If there is already data in any of these cells, it does NOT overwrite it but instead shows a #REF! error message
- You can input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work
- The output from the SPLIT function is an array of values that can be passed as the input into another formula, which may require the use of the Array Formula
Alternative Split Method
There’s an alternative way to split values in a Google Sheet.
Under the Data menu, there’s a feature called “Split text to columns” which will separate single columns into multiple columns, based on the delimiter you specify.
It’s a quick and easy way to split text.
Note that it overwrites existing data in your Sheet if the split columns overlap with any existing data.
SPLIT Function Template
Click here to open a view-only copy >>
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open it in an Incognito window you’ll be able to see it.
You can also read about it in the Google documentation.
Advanced Examples of the SPLIT Formula in Google Sheets
Extract The N-th Word In A Sentence
You can wrap the SPLIT function output with an INDEX function to extract the word at a given position in a sentence. E.g. to extract the 4th word, use this formula:
=INDEX(SPLIT(A1," "),4)
If you combine this with a drop down menu using data validation, you can create a word extractor:
Alphabetize Comma-Separated Strings With The SPLIT Function in Google Sheets
Suppose you have a list of words in a single cell that you want to sort alphabetically:
This formula will rearrange that list alphabetically:
=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))It splits the string of words, applies the TRANSPOSE function to convert into a column so it can be sorted using the SORT function, and then recombines it with the JOIN function.
Read more in Formula Challenge #3: Alphabetize Comma-Separated Strings.
Splitting and Concatenating Strings
The SPLIT is useful in more advanced formulas as a way to divide an array into separate elements, do some work on those elements (e.g. sort them) before recombining them with another function, like the JOIN function.
For example, this array formula will add surnames to a list of first names in a cell:
=ArrayFormula(TRIM(JOIN(", ",SPLIT(A2,", ")&" Smith")))which looks like this in your Google Sheet:
Using the onion framework to analyze this formula, starting from the innermost function and working out, it splits the text string, joins on the surname “Smith”, trims the excess trailing space with the TRIM function, and finally outputs an array by using the Array Formula.
Find Unique Items In A Grouped List
Suppose you want to find unique values from data that looks like this:
You want to extract a unique list of items from the column containing grouped words, which are separated by commas.
Use this formula to extract the unique values:
=ArrayFormula( QUERY( UNIQUE( TRIM( FLATTEN( SPLIT(A2:A100,",")))),"where Col1 is not null order by Col1"))Read more about this technique in this post: Get A Unique List Of Items From A Column With Grouped Words
Unpivot Technique
The SPLIT function in Google Sheets is used in a number of the complex IMPORT formulas for retrieving social media statistics into your Google Sheet.
The SPLIT function was combined with the FLATTEN function in this exceedingly wacky unpivot formula in Google Sheets:
=ArrayFormula(SPLIT(FLATTEN(B1:E1&"🦊"&A2:A4&"🦊"&B2:E4),"🦊"))All in all, SPLIT is a useful function!
Super super super awesome. This tip really healped me. THANK YOU!
That’s great to hear! Thanks, Rodrigo.
I dont know how to use the function
my sample pattern is
02 04 20 21
Ben, thank you so much for this comprehensive article. I’ve been learning a lot from you since I signed up
Thanks, Gary. You’re welcome. Keep up the good work!
Your tips are great and useful. I really appreciate your sharing. Thank you, Ben.
Hi Ben,
If I have a cell like this
A= Mike Tyson, Ken Norton, Joe Frazier, Lennox Lewis
how to count many element in that comma separated values but exclude Mike Tyson?
Hello, Ben! Help me, please!
Question:
how to keep the delimiters in the result?
Example:
1234,56+123
Required result:
1234,56 +123
Note:
the number of characters can be different, so it is wrong to use LEFT or RIGHT formulas.
Brilliant, Very hand for processing forms results from checkboxes where mutiple checkboxes can be selected but is there any way to do this using a query to enable the output to be further manipulated?
This is great stuff!
However, I came across a problem in working in one of my sheets. When I updated the data in my spreadsheet, the data in some of the columns did not change.
Let’s say yesterday the data in row one that I’d be splitting was this: 1234, 5678. So in the columns behind that it was 1234 | 5678.
However today I update it and there are no numbers. But the split columns still retain yesterdays information: 1234 | 5678.
Is there any way around this problem?
Sad to see that these comments are not replied to. Is your spread sheet set to recalculate “On Change”? Check the spreadsheet settings.
Hi Thomas, I reply when I can, but I have a job and kids. Even if I just answered questions for free all day long I wouldn’t get through them all 😉
I came here because I have a problem with splitting a cell with a date in it.
e.g. A2 -> “21 Friday May”.
I want to split that into 3 cells using space as a delimiter.
The results of A3 -> =SPLIT(A2,” “)
is
A2 -> 20
B2 -> Friday
C2 -> May
Every time the function will subtract 1 from the integer.
Why?
Hi Thomas,
When i checked your formula =SPLIT(A2,” “)
i do get correct answer
A2 -> 20
B2 -> Friday
C2 -> May
Helloooo..to everybody!
Great community here!!
I have a very similar problem…but just a bit different..
here my list of codes where the usefull difference are ALL numbers and the the letter Q before the number in some cases..
I need to isolate (split) the letters, with the exception of the Q and numbers
UWARQ2 >>>>>>> UWAR Q2
USUGJQ2>>>>>>> USUGJ Q2
USDAQ1>>>>>>>> USDA Q1
USDA7>>>>>>>>> USDA 7
UHRB7>>>>>>>>> UHRB 7
LWSD2>>>>>>>>> LWSD 2
LWSD1…………
LWGj1………..
LWBP3………
LWBP2………
LUSH3……..
LUGD3……
LUBPAA5>>>>>>> LUBPAA 5
LUBPAA4>>>>>>> LUBPAA 4
Any suggestion ?…
Tks so much in advance!!
Sergio
Breathtakingly good.
If there are two special characters such fox and a heart in the cell. Then how to apply the formula by each in this case.
You could add both to the split and it will use both for the splitting, e.g. where X = emoji 1 and Y = emoji 2 (it won’t display them properly in the comment, sorry):
=SPLIT(text, "XY")Thank you for this! It’s a great help when making reports.
I’d like to ask something too. Would you know a formula to count multiple numbers in one cell that has been separated using Alt+Enter?
Hi Yuka,
That’s great to hear!
For your question, try this formula for numbers:
=COUNT(SPLIT(A12,CHAR(10)))or
=COUNTA(SPLIT(A12,CHAR(10)))if it’s text values.
Hope this helps!
Ben
Hi
I need sheets to suggest words by looking up the entire sheet(words in every cell).
I mean if A1, B2, and many others have multiple words in them, and when I write a word in a new cell that are already present in some other cells, I need that to be suggested.
Why can’t the delimiter for SPLIT be an empty string? Say, for splitting a string into an array of all its characters. I saw the challenge post about it, but the solutions seem like a workaround when it shouldn’t be needed, to me.
Ben, I absolutely love your tips. I use them all of the time for my work at a school district. My question is how can I split text in a cell at either a comma OR the word and without it splitting at every a, n, and d? I have a cell with a list of names that I need to split, then rejoin. For example, I need “Becky, Will and Maddie” to be split to Becky | Will | Maddie. It is perplexing me. Thank you for using your powers for the good of others!
I submitted a comment earlier with a question, and I have since figured it out. I used substitute to replace the word “and” with a comma. You can delete my comments if needed. Thanks!
Is it possible to get the split function to skip a column in-between each output of the split. I have a list of service add ons being inputted into one column. I want to separate out each service add on to its own column but skip a column after each separation so that I can use a vlookup formula to assign a value to the service so that they can be added up automatically to help create determine total value of service add ons to aid in generating an invoice.
Or perhaps there is an even more efficient way that I am unaware of. Thanks for the help!
Hey hi,
I’m looking for an solution to extract a value from array(set of values) and return it by matching row.
Hi
I have two rows
A B
Apple 1,2,3
Banana 8,9,10
I would like to split and transpose Column B and multiply value of Column A based on split rows
A B
Apple 1
Apple 2
Apple 3
Banana 8
Banana 9
Banana 10
Is it possible in google sheets?
Absolutely awesome, especially the “Find Unique Items In A Grouped List” formula!! Would it also be possible to count the appearance of every item before UNIQUE was imposed on it?
For example that a number would be behind every unique word. indicating the amount of appearances?
I stumbled onto a use for Regexreplace inside Split to separate each letter or number in a string, if there are no spaces or punctuation in the string.
=split(regexreplace(“XYZ”,”\B”,”|”),”|”) places each letter from the string in a separate cell.
It works because
=regexreplace(“XYZ”,”\B”,”|”) returns X|Y|Z
And for comparison
=regexreplace(“XYZ”,”\b”,”|”) returns |XYZ|
=regexreplace(“XYZ”,”\b|\B”,”|”) returns |X|Y|Z|
I was making a named function to verify the checksum built into vehicle VIN numbers. I also tried Makearray and Mid, instead of Split and Regexreplace. The Split/Regexreplace combination was faster when applied to a few thousand VINs in a test.
It will not work on strings with spaces or punctuation (i.e., would not work on any character that would match “\W”), which is not an issue in VINs.
Hope somebody finds it useful.