How to Split Cells in Google Sheets
Google Sheets offers three ways to split cells: Data → Split text to columns (destructive, replaces source), the SPLIT function (live formula), and traditional LEFT/RIGHT/MID formulas.
⚡ Quick Answer
Select cells, click Data → Split text to columns, choose a separator from the popup. For a live formula, use =SPLIT(A1, " ") in an empty cell — original stays intact.
Method 1: Data → Split text to columns
Best when you're happy to replace the source with split results.
Select cells to split
Highlight the column of cells.
Click Data → Split text to columns
A small "Separator" dropdown appears at the bottom of your selection.
Choose a separator
Options: Detect automatically, Comma, Semicolon, Period, Space, or Custom (specify any character).
Preview and confirm
The split happens instantly on separator selection. You can change the separator while the dropdown is still visible to try different options.
Split text to columns overwrites the source cells with the split result. If you need to preserve the original, copy your data to a new column first, or use the SPLIT function (Method 2).
Method 2: SPLIT function (non-destructive)
Creates a live formula that updates when source data changes.
Click an empty cell
Where you want the split result.
Enter SPLIT formula
Syntax:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). Example:=SPLIT(A1, " ")splits A1 on spaces.Press Enter
Result spills across adjacent cells. Editing A1 auto-updates the split.
SPLIT examples
=SPLIT("John Smith", " ") → ["John", "Smith"]
=SPLIT("a,b,c", ",") → ["a", "b", "c"]
=SPLIT("a;b,c d", " ,;") → ["a", "b", "c", "d"] (multi-delimiter)
=SPLIT("a,,b", ",", TRUE, FALSE) → ["a", "", "b"] (keeps empty)
SPLIT's third argument treats each character in the delimiter string as a separate delimiter (default TRUE). Pass FALSE to treat the whole string as one delimiter. =SPLIT(A1, ", ", FALSE) only splits on the literal ", " (comma-space).
Method 3: LEFT/RIGHT/MID formulas
Fallback for precise character-based splitting.
Split full name into first and last
First name: =LEFT(A1, FIND(" ", A1) - 1)
Last name: =MID(A1, FIND(" ", A1) + 1, LEN(A1))
Split by first delimiter
Before @: =LEFT(A1, FIND("@", A1) - 1)
After @: =MID(A1, FIND("@", A1) + 1, LEN(A1))
Comparison of methods
| Method | Live updating? | Preserves source? | Best for |
|---|---|---|---|
| Split text to columns | No | No (destructive) | One-time cleanup |
| SPLIT function | Yes | Yes | Live results, source changing |
| LEFT/RIGHT/MID | Yes | Yes | Character-precise splits |
| REGEXEXTRACT | Yes | Yes | Complex pattern extraction |
Advanced: REGEXEXTRACT for complex patterns
=REGEXEXTRACT(A1, "([0-9]+)") → extracts first number sequence
=REGEXEXTRACT(A1, "^([^ ]+)") → extracts everything before first space
=REGEXEXTRACT(A1, "@(.+)$") → extracts domain from email
Sheets Wizard — split messy data no delimiter can handle
SPLIT works when data is clean. Real data is messy: "John Q. Smith Jr., PhD" or "555 Main St #4B". Sheets Wizard's AI understands what you want ("split into first name, last name, suffix") and does it, no formulas required. Handles inconsistent formats across rows.
Install Sheets Wizard →Frequently asked questions
How do I split a cell in Google Sheets?
Select the cells you want to split. Click Data → Split text to columns. A small dropdown appears near the data — choose a separator (Comma, Semicolon, Period, Space, Custom, or Detect automatically). Sheets splits the values across adjacent cells.
How do I split without destroying the original data?
Use the SPLIT function in a new location: =SPLIT(A1, " ") splits A1 on spaces into multiple cells while keeping A1 intact. The result spills across adjacent cells and updates automatically when A1 changes.
How do I split a full name into first and last name in Sheets?
Use =SPLIT(A1, " ") to split on the first space. Or Data → Split text to columns with Space as the separator (destructive). For more control, use =LEFT(A1, FIND(" ", A1)-1) for first name and =MID(A1, FIND(" ", A1)+1, LEN(A1)) for last name.
Can I split cells by multiple delimiters at once?
Yes with SPLIT: =SPLIT(A1, " ,;") splits on space, comma, or semicolon — the third argument treats each character as a separate delimiter. Data → Split text to columns doesn't support multiple delimiters at once; you'd need to run it multiple times.