Split a Google Sheets Column Into Two Columns

To split a column in Google Sheets, select the cells, choose Data > Split text to columns, then pick the separator that divides each value. For a formula-based result that updates when the source changes, use SPLIT, such as =SPLIT(A2," ") when A2 contains two words separated by a space.

Use Split text to columns for a quick one-time split

The built-in command is usually the simplest choice when you want to separate existing text and keep the results as ordinary cell values. It works well for names, addresses, product codes, and other data that follows a consistent pattern. In Google Sheets in a web browser, the basic steps are:

Select the cell or range containing the combined values. You can select a whole source column if its rows follow the same pattern.
Choose Data > Split text to columns.
Use the separator control that appears to select the character or pattern between the parts. Choose a preset such as comma, semicolon, period, or space, or choose a custom separator and enter the character you need.
Review the resulting columns, then check several rows to make sure each value landed in the intended place.

For example, if A2 contains Jordan Lee, choosing a space separator puts Jordan in one cell and Lee in the next. The command separates the selected text in place; it is not a live link back to the original cell. If the original values need to remain available, make a copy of the source column first or use a formula in an empty area.

Choose a separator that matches the data

A separator is the character, or sequence of characters, that marks the boundary between fields. The right choice depends on how the source text was assembled. A comma is common in lists and exported data, spaces are common in names, and hyphens or slashes often divide codes or dates. Look at several rows before splitting: a separator that works for one example may occur inside another value.

Comma: North,West becomes North and West.
Space: Jordan Lee becomes Jordan and Lee.
Hyphen: AB-204 becomes AB and 204.
Custom text: Order 48 | packed can be split at the vertical bar if that character is the true boundary.

Be cautious with spaces in full names or addresses. Splitting Jordan Lee Park at every space creates three values, not just first and last name. Likewise, splitting 12 Main Street, Apt 4 at commas creates two parts, but splitting at spaces fragments the street address. A spreadsheet cannot infer the meaning of a separator; it follows the rule you specify.

Use the SPLIT formula for results that update

The SPLIT function divides text around a delimiter and returns the parts in adjacent cells. It is useful when the source data may change, when you want to preserve the original column, or when you need a repeatable calculation. Put the formula in an empty cell to the right of the source data so its results have room to expand.

With a full name in A2 and one space between the first and last names, enter this in B2:

=SPLIT(A2," ")

If A2 contains Jordan Lee, the result is Jordan in B2 and Lee in C2. Copy the formula down to apply it to more rows. The formula result is linked to A2, so changing A2 updates the split values. Keep the cells to the right of each formula output clear; existing content can prevent the result from expanding.

For a comma followed by a space, such as Lee, Jordan, the formula can use that complete two-character delimiter. With the combined text in A2, enter this in B2:

=SPLIT(A2,", ",FALSE,TRUE)

The FALSE argument tells Sheets to treat the delimiter as a complete string rather than splitting on each character in it. The final TRUE argument removes empty results caused by repeated delimiters. These optional settings are helpful when punctuation and spacing are meaningful parts of the boundary.

Understand multiple separators and empty values

By default, SPLIT treats each character in the delimiter as a possible separator. For instance, a delimiter entered as ,- can split at a comma or at a hyphen. This is convenient when either mark should divide the text, but it is not the same as looking for the exact two-character sequence comma-hyphen. Set the third argument to FALSE when the entire delimiter string must match.

The function also normally removes empty results between adjacent separators. Imagine a source value with two commas in a row: red,,blue. If empty text is removed, the output may contain only red and blue; if the blank middle field represents real missing information, set the fourth argument to FALSE to retain it. With that exact value in A2, use:

=SPLIT(A2,",",TRUE,FALSE)

The output is red, a blank cell, and blue. This formula may produce three columns, which illustrates an important limitation: SPLIT separates at every matching delimiter. It does not promise exactly two outputs. If the source has more than one occurrence, check whether all occurrences should become boundaries before applying the formula down a dataset.

Prepare the data and protect the source

Before changing a large range, check whether the columns beside it contain information. A split can occupy cells to the right, so a populated destination area risks being overwritten or can block formula results. The safest approach is to duplicate the source column or split into an unused area, then compare a sample of the results with the originals.

Also inspect consistency. These values appear similar but do not have the same separator: AB-204, AB - 204, and AB/204. A single hyphen separator will not handle all three in the same way. Decide whether to standardize the text first or use a formula that matches the actual patterns. Extra spaces at the beginning or end of values can also create apparently blank pieces. If the split is for a one-off cleanup, inspect and correct those rows rather than assuming every blank output is meaningful.

For a non-destructive workflow, place formulas in a spare area, confirm the output, then copy the results and use Edit > Paste special > Values only if you need fixed values rather than formulas. Keep the original column until you have verified the new fields and any downstream formulas or charts that rely on the old layout.

Common problems and how to fix them

Everything stays in one column: Check that the selected separator exactly matches the text. A comma followed by a space is not always equivalent to a comma alone, especially when spaces are retained or are part of the intended pattern.
Values split into too many columns: The chosen character appears more than once in some cells. Review those rows and choose a more specific delimiter, or use a method designed for the particular structure.
A formula result will not expand: Clear the cells where the returned parts need to go. A value, a space, or another formula in the output area can block an array result.
Some rows look shifted: The records may use inconsistent separators, or some values may contain the separator as part of the content. Test representative rows before applying the split to the entire column.
Blank results appear: Repeated delimiters or spaces around a boundary may create empty pieces. Decide whether blanks represent missing fields or unwanted spacing before removing them.

A common misconception is that splitting at a comma always correctly parses a CSV record. A simple delimiter split is not a full CSV parser: quoted fields can contain commas that are part of the value. If you are working with an actual CSV file, import the file through Google Sheets rather than blindly splitting a column of raw CSV text, and verify how quoted fields and delimiters were interpreted.

Choose between the menu, a formula, and other tools

Use Data > Split text to columns when you want to transform existing cells once and the separator is clear. It is quick and does not require writing a formula. Choose SPLIT when the source should stay intact, the source may change, or you want the transformation to be visible and repeatable. For a simple, consistent pattern, both approaches can be effective; the key difference is that the menu action produces a direct split while the formula recalculates from its input.

If your data does not have a reliable delimiter, neither option can reliably guess where one field ends and another begins. For example, a list of people with middle names cannot be separated into first and last names just by splitting at every space. You may need a clear business rule, manual review, or a more structured source export. For complex text extraction, Google Sheets also has text functions such as REGEXEXTRACT, but regular expressions require a pattern that correctly describes the data; they are not automatically safer than a simple split.

Excel has a similarly named feature, but its desktop command is generally Data > Text to Columns, not Google Sheets’ Data > Split text to columns. Instructions and options are not identical between the programs. If you are following steps for Sheets, use the Sheets menu in a browser rather than looking for the Excel ribbon command.

Frequently Asked Questions

How do I split a column in Google Sheets by a space?

Select the cells, choose Data > Split text to columns, and select space as the separator. To use a formula with the text in A2, enter =SPLIT(A2," ") in an empty cell. Check that the values do not contain extra spaces or multiple-word names that should stay together.

Can Google Sheets split one cell into exactly two columns?

Yes, if each value contains one occurrence of the separator and that occurrence marks the boundary. The menu and SPLIT divide at matching separators, so a value with several matches can create more than two output columns. Check the source pattern before relying on an exactly two-column result.

Can I split a column and keep the original data?

Yes. Use a formula such as =SPLIT(A2,"-") in a blank area, with the source value in A2. The original remains unchanged, and the formula output updates if A2 changes. Alternatively, duplicate the source column before using the menu command.

Why does Split text to columns put text in the wrong places?

The selected separator may occur inside a field, or different rows may use different punctuation or spacing. Inspect several examples, including unusual rows, and choose a delimiter that consistently identifies the boundary. If the data is inconsistent, standardize it or handle exceptions separately.

Does SPLIT work when the delimiter is more than one character?

Yes. Use the full delimiter as the second argument and set the third argument to FALSE if it must be matched as a complete string. For example, =SPLIT(A2," - ",FALSE,TRUE) splits at a space, hyphen, space sequence rather than treating each character as a separate delimiter.

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.