How to Split Text Into Columns in Google Sheets
=SPLIT(text, delimiter), which leaves the original untouched. For a delimiter of several characters that must match as one unit, add FALSE as the third argument.
Two ways to split text
You can split text with a menu command or with a formula. The menu is quick for a one-off clean-up. The formula keeps the original and updates itself when the source text changes.
The example
Here are two pieces of text in cells A1:A3.
| A | |
|---|---|
| 1 | Text |
| 2 | Ann|Lee|Sales |
| 3 | salt-and-pepper |
Way 1: the Split text to columns menu
Following Google's help page:
- Select the cells you want to split.
- Click Data, then Split text to columns.
- To change the character used to split, open the dropdown next to Separator. Choosing Detect automatically lets Sheets pick one.
Because the menu writes the pieces into the cells to the right, make sure that area is free before you run it.
Way 2: the SPLIT formula
To split the first text at each vertical bar:
=SPLIT(A2, "|")
This puts Ann, Lee and Sales in three cells side by side. The separator is removed from the output. The SPLIT guide has the full syntax.
Separators of more than one character
By default, SPLIT treats every character of the delimiter as its own split point (split_by_each is TRUE). For salt-and-pepper, a delimiter of "-and-" would also cut at every a, n, d and hyphen. To split only where the whole string appears, set the third argument to FALSE:
=SPLIT(A3, "-and-", FALSE)
This returns salt and pepper in two cells.
Empty pieces
By default, consecutive delimiters count as one, so no empty cells appear. If you need an empty cell where a value is missing, such as in a,,c, set the fourth argument remove_empty_text to FALSE: =SPLIT(A2, ",", TRUE, FALSE).
Common mistakes
- Blocked output cells. Cells to the right must be empty.
- Multi-character separators without FALSE. Text is cut in too many places.
- Splitting names that contain the separator. A name with a space in it will split in two when you split on spaces.
Try it yourself
Use the text above (A2 and A3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Split at a bar
Write a formula that splits the text in A2 at each vertical bar (|).
Exercise 2: Whole-string separator
Write a formula that splits A3 only where the whole string -and- appears.
What to learn next
- SPLIT: break text into separate cells.
- TEXTJOIN: combine text from many cells with a separator.
- LEFT, RIGHT and MID: pull part of a text string by position.
Syntax and rules in this guide were checked against Google's Split text into columns help page. See all guides.