Google Sheets: How to Split Text in a Cell or Column

To split text in Google Sheets, select the cell or range, then choose Data > Split text to columns and select a separator. To split with a formula instead, use =SPLIT(A2, ",", TRUE, TRUE) when the text to separate is in A2 and commas mark the boundaries.

Choose the right way to split text

The menu command is usually best for a one-time cleanup: it turns the contents of each selected cell into separate values in neighboring columns. A formula is more useful when you want the result to update automatically as the original text changes, or when you want to keep the original cell untouched.

Before choosing, consider what the separator represents and how consistent the source data is. A comma-separated list is straightforward; a person’s full name may not be. Splitting a name at the first space, for example, will not reliably distinguish a first name from a middle name or a compound surname. The method can divide characters at a boundary, but it cannot infer missing structure that the text does not clearly provide.

Use Split text to columns for a quick, visible conversion of one or more rows.
Use SPLIT when you need a repeatable formula result or want to preserve the source.
Inspect the data first if separators are inconsistent, appear inside values, or are missing from some rows.

Split a cell with the menu

In Google Sheets in a web browser, the built-in command is under the Data menu. It works on a selected cell or a range, so it can process a column of similarly formatted entries in one operation.

Select the cell or range containing the text. If you are working with a list, select the full source range, not just its first row.
Choose Data > Split text to columns.
Use the separator control that appears near the selected data. Choose a listed separator, such as comma, space, semicolon, or tab, or choose Custom and enter the character or text that divides the fields.
Check the split results in the columns to the right. If the boundaries are wrong, change the separator or undo the operation and try again.

For example, splitting Nguyen, Linh, Seattle with a comma separator produces three values: Nguyen, Linh, and Seattle. If the source is Nguyen, Linh and the intended result is a surname and a given name, that same separator produces two fields, but the comma and following space may leave a leading space in the second field. You can account for that with a custom separator containing comma-space, or clean the result afterward.

Make sure there is room for the output. Splitting one cell into three pieces needs space in the neighboring two columns on that row. Existing values in the destination area can be replaced by the split results, so copy the source to a clear area or make a copy of the sheet first if the surrounding cells contain information you need. The original text is distributed across the output cells; this command is not a way to keep an untouched copy automatically.

Choose a separator that matches the data

A separator is the boundary Sheets looks for. Common choices include commas in CSV-style text, semicolons in some exports, tabs between copied fields, and spaces between words. The automatic separator option can be convenient when the pattern is obvious, but check the output rather than assuming it identified the intended structure. A character may also occur inside a value: splitting an address at every comma, for instance, can separate street, city, and region, but may not match the columns you ultimately want.

Spaces deserve particular care. If you split a full name on every space, Mary Ann Smith becomes three pieces, not two. Likewise, splitting a product description on spaces may scatter words across many columns. A delimiter that is unlikely to appear inside a field, such as a pipe character (|), is often safer when you control how the source text is created.

When the delimiter is more than one character, distinguish the whole delimiter from its individual characters. The formula option SPLIT treats each character in a delimiter string as a separate possible separator by default. Thus, using comma-space as the delimiter can split on either the comma or the space unless you change the relevant argument. The menu’s custom separator is often simpler for a one-time split; the formula section below shows how to specify an exact multi-character delimiter.

Use the SPLIT formula

The Google Sheets SPLIT function returns pieces of text in neighboring cells. Its syntax is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The square brackets indicate optional arguments; do not type the brackets into the formula. The first argument is the text or cell to divide, and the second is the separator.

With the text apples,oranges,pears in A2, enter =SPLIT(A2, ",", TRUE, TRUE) in B2. The result spills across B2:D2 as apples, oranges, and pears. Here, TRUE means each character in the delimiter is treated separately, and the final TRUE removes empty pieces. Those are the function’s default behaviors, but writing the arguments makes the choices clear.

To treat comma-space as one exact separator, put Lee, Jordan, West in A2 and enter =SPLIT(A2, ", ", FALSE, TRUE) in B2. The result is Lee, Jordan, and West. The third argument is FALSE, so Sheets looks for the two-character delimiter as a unit rather than splitting separately at commas and spaces. Leave enough empty cells to the right for all returned pieces; a value already occupying a destination cell can prevent the array result from expanding.

Copy a formula down a column to process additional rows, adjusting the source reference for each row. For instance, if each record is in column A, place the formula in B2 and fill it down. The formula result recalculates when the source text changes, unlike a one-time menu split. If you later need fixed values rather than formulas, copy the results and use Edit > Paste special > Values only.

Empty fields and inconsistent separators

Repeated separators can create empty fields or shift the expected positions. Consider red,,blue: the two commas indicate an empty item between red and blue. With SPLIT, the fourth argument controls whether empty text between consecutive delimiters is removed. When it is TRUE, empty pieces are removed; when FALSE, they are retained. If a blank field carries meaning—for example, a missing middle value in a fixed sequence—do not remove it without checking how that changes the columns.

Real-world text may also mix separators, such as commas in some rows and semicolons in others. A single menu separator or simple SPLIT formula cannot guess every intended boundary. Standardize the source first, or work through a copy and check representative rows. Also check whether spaces around delimiters are consistent: red, blue and red ,blue do not contain the same exact comma-space pattern.

When a separator appears inside a value, splitting on every occurrence can produce too many columns. For example, a comma inside a quoted description may be data rather than a field boundary. If the text came from a properly structured CSV file, importing it as a CSV is generally safer than treating its contents as ordinary text, because quoted fields can require special handling. For hand-built strings, choose an unambiguous delimiter or repair the source format before splitting.

Keep the source and organize the result

If preserving the original is important, put a formula in a separate area or duplicate the source column before using the menu command. Keep a header that explains the new fields, and check several rows after a large split. A single unusual record can have more separators than the rest and push later values into extra columns.

Sometimes the desired output is vertical rather than horizontal. SPLIT returns pieces across columns; if you need one piece per row, you can wrap the result in TRANSPOSE. With north|south|east in A2, enter =TRANSPOSE(SPLIT(A2, "|", FALSE, TRUE)) in an empty vertical range. It returns north, south, and east down the rows. As with a horizontal result, the cells the formula needs to fill must be clear.

After splitting, review the data type as well as the visible text. A piece that looks like a number, date, postal code, or identifier may need different treatment. Postal codes and account-like IDs can contain leading zeroes; converting them to numeric values can remove those zeroes. If exact characters matter, keep such fields as text and verify them before using the data in another system.

Google Sheets compared with Excel

In Google Sheets, the menu command is Data > Split text to columns, and the formula discussed here is SPLIT. Microsoft Excel has different tools and function names, so instructions for Excel may not match the Sheets menus or formulas. In current Microsoft 365 Excel, the comparable formula is TEXTSPLIT; Excel’s menu options and availability can also depend on the version. Do not paste an Excel TEXTSPLIT formula into Sheets expecting it to work. If you are following a guide, first confirm that it is written for Google Sheets rather than Excel.

Frequently Asked Questions

Can I split only one cell?
Yes. Select the cell and choose Data > Split text to columns, then pick the separator. If you want to retain the original value, use a formula in a different cell instead of splitting the source in place.

How do I split text at a space?
Select the relevant cells, choose Data > Split text to columns, and choose space as the separator. This splits at each space, so a phrase with several words will create several pieces. Use a more specific separator or another approach if you need only the first space to count.

How can I split on comma-space as one delimiter?
Use SPLIT with the third argument set to FALSE. For text in A2, =SPLIT(A2, ", ", FALSE, TRUE) treats comma-space as a single boundary instead of treating comma and space separately.

Why does my formula result not appear across the row?
Check that the formula syntax and delimiter match the source, and that the cells to the right are empty. A formula that returns multiple pieces needs room to expand. Also check whether the source contains the separator you specified exactly.

Can I split text into rows instead of columns?
Yes. Wrap SPLIT in TRANSPOSE, for example =TRANSPOSE(SPLIT(A2, "|", FALSE, TRUE)). Enter it where there is enough clear space below for the returned pieces.

For a fast cleanup, use the menu and verify that the destination cells are clear. For repeatable results, use SPLIT with a delimiter that matches the actual text pattern, and preserve the source until you have checked the output.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.