How to Split Text Into Columns in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 6 min read · includes 2 free exercises

In short: Select the cells, then choose Data > Split text to columns. Or use =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
1Text
2Ann|Lee|Sales
3salt-and-pepper

Way 1: the Split text to columns menu

Following Google's help page:

  1. Select the cells you want to split.
  2. Click Data, then Split text to columns.
  3. 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

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 (|).

Hint: SPLIT(A2, "|").

Exercise 2: Whole-string separator

Write a formula that splits A3 only where the whole string -and- appears.

Hint: set the third argument, split_by_each, to FALSE.

What to learn next

Syntax and rules in this guide were checked against Google's Split text into columns help page. See all guides.