SPLIT and JOIN practice in Google Sheets
Cut text into cells with SPLIT and put a list back together with JOIN.
How SPLIT and JOIN work in Google Sheets
SPLIT cuts text into pieces and puts each piece in its own cell. It takes the text and the character to cut at, called the delimiter. JOIN does the reverse: it takes a delimiter and a range, and glues the cells into one text with the delimiter between them. In Excel 365, TEXTSPLIT does the job of SPLIT.
=SPLIT(A2," ") turns the name Anna Smith in A2 into Anna in B2 and Smith in C2. The pieces spill into the cells to the right, so those must be empty. =JOIN(", ",A2:A4) turns the three tags in A2:A4 into one text such as red, green, blue.
Watch out for delimiters longer than one character. By default SPLIT cuts at each character on its own, so =SPLIT(A2,", ") also cuts at every space, and "New York, Boston" ends up in three cells. Add FALSE as a third argument to cut only at the exact text: =SPLIT(A2,", ",FALSE). The exercises below cover splitting a name, splitting a date at the dashes and joining a list with JOIN.
- 1Split a name with SPLITIn B2, use SPLIT to split the name in A2 at the space, into first and last name.
- 2Split a date into numbersPremiumIn B2, use SPLIT to split the date text in A2 at the dashes into year, month and day.
- 3Join a list with JOINPremiumIn B2, use JOIN to turn the tags in A2:A4 into one text, with a comma and a space between them.
Functions in this topic
Questions
How do I split text at a dash or a slash?
Put both characters in the delimiter: =SPLIT(A2,"-/") cuts at every dash and every slash, so 2026/03-15 becomes 2026, 3 and 15. That works because SPLIT treats each character in the delimiter as its own cut point, unless you add FALSE as the third argument.
Why does SPLIT remove the zero from 03?
Sheets turns pieces that look like numbers into real numbers, so 03 shows as 3. The value is the same, only the display changes. To show two digits again, select the cells and apply a custom number format of 00. The numbers stay numbers, so you can still calculate with them.
How do I combine first and last name in Google Sheets?
Use =JOIN(" ",A2:B2) to put a space between the two cells, or =A2&" "&B2 if you only join two. JOIN is easier when you have many cells, because you give it the range and the delimiter once. Fill the formula down to do the same for every row.