SPLIT function in Google Sheets - Step-by-Step Guide

Introduction

The SPLIT function breaks text into separate pieces based on a character you choose. It helps you separate words or numbers that are stuck together in one cell into multiple cells.

When you have a list of full names in one cell and want to separate first and last names.

When you receive data with items separated by commas and want each item in its own cell.

When you want to split a date or time that is written as text with dashes or colons.

When you want to separate a list of email addresses separated by semicolons.

When you want to break apart product codes that use a special symbol as a divider.

Steps

Step 1: Click

- a blank cell where you want the split results to start

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the SPLIT formula

💡 Start with an equal sign (=) to begin the formula

Step 3: Enter

- the formula bar

The formula is complete and ready to run

💡 Use the syntax: =SPLIT(text, delimiter)

Step 4: Press

- Enter key

The text splits into separate cells based on the delimiter

Step 5: Adjust

- the delimiter in the formula if needed

The split changes to match the new delimiter

Before vs After

Before

Cell A1 contains: "apple,banana,orange" all in one cell

After

Cells B1, C1, and D1 contain: "apple" | "banana" | "orange" each in its own cell

Settings Reference

text

📍 first argument in the SPLIT formula

The text you want to split

Default: none

delimiter

📍 second argument in the SPLIT formula

The character(s) that mark where to split the text

Default: none

split_by_each

📍 optional third argument in the SPLIT formula

If TRUE, splits at each character in delimiter; if FALSE, splits only at the whole delimiter string

Default: TRUE

remove_empty_text

📍 optional fourth argument in the SPLIT formula

If TRUE, empty cells from consecutive delimiters are removed

Default: TRUE

Common Mistakes

Using the wrong delimiter character in the formula

The SPLIT function won't separate the text correctly if the delimiter does not match the actual separator in the text

Check the text carefully and use the exact character(s) that separate the parts, like a comma, space, or dash

Not putting text strings inside quotes

Google Sheets treats unquoted text as cell references, causing errors if the cell does not exist

Always put text strings in double quotes, for example: =SPLIT("apple,banana", ",")

Expecting SPLIT to work vertically by default

SPLIT outputs results horizontally across columns, not down rows

Use TRANSPOSE function if you want to convert the horizontal split results into a vertical list

Summary

SPLIT breaks text into multiple cells using a chosen separator.

You must specify the text and the delimiter in the formula.

By default, SPLIT separates text horizontally across columns.