SPLIT and JOIN functions in Google Sheets

SPLIT divides a text into separate cells at a chosen character; JOIN puts the values of a range together with a separator.

Data often arrives with several facts squeezed into one cell: a full name, a code with parts, a list separated by commas. SPLIT pulls them apart into neighbouring cells. JOIN does the opposite. Excel had no SPLIT function for most of its life, and this pair is one of the reasons people like Sheets for tidying data.

Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

=JOIN(delimiter, value_or_array1, [value_or_array2], ...)

Argument	What it is
`text`	The text to divide.
`delimiter`	The character or characters to split at, or to put between joined values.
`split_by_each`	TRUE (the default) treats each character of the delimiter as a separate place to split. FALSE splits only at the whole delimiter.
`remove_empty_text`	TRUE (the default) drops empty pieces, such as those between two commas in a row.

Examples

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=SPLIT(A3," ")
Result:
RAVI KUMAR
A name divided at the space into two cells.
=SPLIT(B2,"@")
Result:
asha.verma example.com
An email address divided at the @.
=SPLIT(C2,"-")
Result:
PB 10 1234
A code divided at the dashes. The pieces that look like numbers become numbers.
=SPLIT(B2,".@")
Result:
asha verma example com
With two characters as the delimiter, the text is split at every dot and every @.
=SPLIT("a,b,,c",",")
Result:
a b c
Two commas together mark an empty piece. Empty pieces are dropped by default, so three cells come back, not four.
=INDEX(SPLIT(B2,"@"),1,2)
Result: example.com
INDEX takes one piece: the second.
=JOIN("-",LEFT(C2,2),RIGHT(C2,4))
Result: PB-1234
JOIN puts values together with a separator.
=JOIN(", ",C2:C5)
Result: PB-10-1234, DL-08-0042, MH-12-9876, RJ-14-0007
A whole range joined into one cell.

How SPLIT works

Give SPLIT the text and the character to cut at. The pieces land in the cells to the right of the formula, one per cell. Those cells must be empty.

To split downwards, into a column, wrap it in TRANSPOSE: =TRANSPOSE(SPLIT(A2, ",")).

The delimiter is a set of characters

By default, each character of the delimiter is a cutting point. SPLIT(B2, ".@") cuts at every dot and at every @, as the fourth example shows. That is useful, and a surprise if you meant to cut at a two-character marker.

To cut only at the whole marker, set the third argument to FALSE: =SPLIT("a - b-c", " - ", FALSE) gives "a" and "b-c".

Empty pieces

Two delimiters in a row mark an empty piece. By default SPLIT throws these away, which shifts the later pieces to the left. When the position of each piece matters, as with fixed columns of a record, keep them: =SPLIT(A2, ",", TRUE, FALSE).

Spaces and numbers

SPLIT does not trim. After splitting at a comma, pieces keep the space that followed the comma. Split at comma-and-space with the third argument FALSE, or wrap in TRIM: =ARRAYFORMULA(TRIM(SPLIT(A2, ","))).
Pieces that look like numbers or dates are turned into numbers and dates. That is usually welcome, but a piece such as "0042" becomes 42 and loses its zeros. If the zeros matter, extract the piece as text with REGEXEXTRACT or MID instead.

Picking one piece

INDEX chooses a piece by position: =INDEX(SPLIT(A2, " "), 1, 1) is the first word. The last word, when the number of words varies, is =INDEX(SPLIT(A2, " "), 1, COUNTA(SPLIT(A2, " "))).

A whole column at once

SPLIT works inside ARRAYFORMULA on a column of cells: =ARRAYFORMULA(SPLIT(A2:A10, " ")). Each row is split across its own cells.

The menu offers the same as a one-time action: Data, Split text to columns.

JOIN

JOIN takes a separator and one or more ranges, and returns a single text with the separator between the values. It accepts a row or a column, not a block of both.

JOIN includes empty cells, so a gap in the range leaves two separators together. TEXTJOIN has an option to skip blanks and is the better choice for addresses and other lists with gaps.

Join with a line break to stack values in one cell: =JOIN(CHAR(10), A2:A5).

Split, change, join again

The two work as a pair. To put the words of a cell in alphabetical order: =JOIN(" ", SORT(TRANSPOSE(SPLIT(A2, " ")))). To remove repeated items from a comma-separated list: =JOIN(", ", UNIQUE(TRANSPOSE(SPLIT(A2, ", ", FALSE)))).

Common mistakes

Cells to the right not empty, giving #REF!.
A multi-character delimiter cutting in more places than intended.
Leading zeros lost from numeric pieces.
Pieces carrying a leading space.
#VALUE! from SPLIT on an empty cell. Guard with IF(A2="", "", SPLIT(...)).

In Excel

Excel has no SPLIT or JOIN. Microsoft 365 has TEXTSPLIT, with a different layout of arguments, and all recent versions have TEXTJOIN. In other versions of Excel, splitting is done with LEFT, MID and FIND, or with Text to Columns.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.