How to split text into columns by a delimiter

There are two ways, and Grid by Zaweba has both. The TEXTSPLIT function breaks a piece of text apart at a delimiter you choose, such as a comma, a space or a slash, and spills the pieces into neighbouring cells. Text to Columns does the same job as a one-off command, replacing the original column with several new ones.

Picking the right method

TEXTSPLIT is a formula, so it keeps the original text and recalculates if that text changes. That's the better choice when the data keeps arriving, for example a column pasted from an online form each week. Text to Columns changes the sheet itself and leaves plain values behind, which is tidy when you've imported a list once and simply want it in proper columns.

Splitting with TEXTSPLIT

In the first empty cell to the right of your data, type =TEXTSPLIT( then click the cell holding the text, add a comma, and give the delimiter in quotes, for example ", " for a comma followed by a space. Close the bracket and press Enter. The parts spill across the row, and Grid draws a blue spill outline to show the area they fill.

TEXTSPLIT can also split down rows instead of across columns, using a second delimiter argument. Give it both and it builds a small grid from text such as a list of items separated by semicolons, with each item's parts separated by commas.

If the spilled parts would land on cells that already hold something, you'll see #SPILL!. Clear the cells to the right and the result appears.

Splitting with Text to Columns

Select the column you want to split, choose Text to Columns, and tell Grid what separates the parts. The column is divided into as many columns as there are parts. Make sure there are enough empty columns to its right first, or move the data so there are, because the new columns need room.

Undo covers this, as it covers everything in Grid, so if the split goes wrong you can step back and try a different delimiter.

Common jobs and how to handle them

Names are the classic example. A column of full names split at the space gives first and last names, until someone has a middle name. For those cases, TEXTBEFORE and TEXTAFTER are often cleaner: text before the first space is the first name, text after the last space is the surname.

Addresses usually separate parts with commas, which makes them easy to split, but watch for a comma followed by a space. Split on the comma alone and every part after the first starts with a space; split on comma and space together, or tidy the results with TRIM.

Codes such as AB-1234-X split neatly on the hyphen. If you only need one part, there's no need to split everything: TEXTBEFORE or TEXTAFTER, or one of the REGEX functions for a more complex pattern, will extract just that part.

Checking split numbers and dates

Numbers that come out of a split may arrive as text, which matters if you plan to add them up. A quick SUM of the column will tell you: if it gives zero for figures that clearly aren't zero, they need converting. VALUE in a helper column will do it.

Dates are similar. A date split out of a longer string may be text in an order your system doesn't expect, so check one or two before relying on them.

Splitting text is one of those chores every spreadsheet user meets sooner or later. In Grid by Zaweba it takes either one formula or one command, depending on whether you want the split to keep up with the data or simply be done.