Can You Split a Column in Google Sheets? Here’s How

Can You Split a Column in Google Sheets? Here's How

Yes. In Google Sheets, select the cells you want to divide and choose Data > Split text to columns, then pick a separator such as a comma, space, or custom character. You can also use the SPLIT function when you want a formula-driven result that updates as the source text changes.

What splitting a column does

Splitting separates text in each selected cell wherever a chosen delimiter appears, placing the pieces into neighboring columns. For example, a cell containing North,West can become two cells: North and West. The operation is useful for turning a combined field into separate fields that are easier to sort, filter, or analyze.

It does not usually mean dividing a column into two equal-width sections. The result depends on the separator in the text. A list of names separated by commas is straightforward; a column containing inconsistent formats, such as some names separated by commas and others by spaces, may need cleaning first.

Use Split text to columns

For a one-time cleanup, the built-in command is usually the quickest option. In Google Sheets in a web browser:

Select the cells containing the text you want to split. You can select a range in one column or just the relevant cells.
Choose Data > Split text to columns.
Use the separator control that appears to choose a separator or select a custom one.
Check the resulting columns, then undo immediately with Ctrl+Z on Windows or ⌘+Z on a Mac if the split is wrong.

The command processes the selected cell contents and places separated pieces in columns to the right. Before using it, check that those destination cells are empty or that their contents are safe to replace. For important data, duplicate the sheet or copy the source column to a temporary area first. Splitting is an edit to the sheet, not a new view that preserves the original column automatically.

Choose a separator that matches the data

A separator is the character or text marking the boundary between values. Google Sheets can detect a separator automatically, or you can choose one of the offered options, such as a comma, semicolon, period, or space. The custom option is useful for a delimiter such as a hyphen or vertical bar. Inspect a few rows before accepting an automatic choice: detection can be unsuitable when the column contains inconsistent patterns.

Comma: Use for entries such as red,blue,green, provided commas do not also appear inside a value.
Space: Useful for a simple two-part name, but risky for full names with middle names or multiword surnames.
Custom character: Use a distinctive marker, such as a pipe (|), when ordinary punctuation also occurs in the values.
Automatic detection: Convenient for consistent data, but verify the preview or output across different rows.

A frequent misconception is that choosing a space will reliably split a person’s name into first and last name. It actually splits at spaces, so Ana María Lopez may become three pieces. If the data has no consistent rule for identifying the surname, a separator alone cannot infer the intended name structure.

Examples of useful splits

Suppose column A contains product codes in the format QZ-381. Choosing a hyphen as the custom separator creates a prefix and a number in separate columns. The same approach works for consistently formatted location codes such as CA|San Diego, using the vertical bar as the separator.

For a contact list with entries formatted as Pat Lee, a space may produce first and last name columns if every entry has exactly two parts. But if some records include titles, middle names, or compound surnames, splitting the whole list on spaces produces extra columns for those records. Decide whether that result is acceptable before applying the operation to a large range.

Splitting can also help prepare imported data for analysis. A combined category such as Home / Lighting can be separated into a broad category and a subcategory using a slash as a custom delimiter. Afterward, check that blank values and unusual entries have not shifted the meaning of the columns.

Use the SPLIT formula for repeatable results

If the source text may change, a formula can be preferable to a one-time menu operation. In Google Sheets, SPLIT returns the separated pieces across adjacent cells. With the value Maya Chen in A2, enter this in B2:

With the text Maya Chen in A2, this formula returns Maya in B2 and Chen in C2: =SPLIT(A2," ")

For comma-separated values, the same pattern uses a comma as the delimiter. With North,West in A2, this returns North and West in adjacent cells: =SPLIT(A2,",")

Because the formula result expands horizontally, keep the cells to its right clear. If another value blocks the output, Sheets may show an error rather than place all the pieces. To apply the formula to more records, copy it down the rows or use the fill handle. Each row’s formula should refer to that row’s source cell.

By default, SPLIT treats each character in a delimiter string as a possible separator and removes empty pieces. Its optional arguments can change those behaviors. For example, if the delimiter is a two-character sequence that must be matched as a whole, set the third argument to FALSE. A formula is live: changing A2 changes its displayed split result, unlike a one-time menu split. The function is specific to Google Sheets; Excel uses different functions for comparable formula-based work.

Handle fixed positions and irregular text differently

Not every column has a delimiter. If every code uses fixed character positions, the menu command is not the right tool. For example, if A2 contains AB2047 and the first two characters are always the prefix, you can extract them with LEFT; the remaining characters can be extracted with MID. These functions are available in Google Sheets and Excel.

With AB2047 in A2, this returns AB: =LEFT(A2,2). With the same source value in A2, this returns 2047: =MID(A2,3,4).

For messy data, first identify the actual patterns. You might standardize inconsistent punctuation, remove unwanted spaces, or separate exceptional records manually. If the data has no dependable delimiter or fixed structure, splitting automatically can create plausible-looking but incorrect columns. Review edge cases rather than assuming every row follows the most common format.

Prevent common problems and protect data

Most splitting problems come from selecting the wrong range, choosing a delimiter that appears within values, or overlooking existing data in the output area. A brief check before running the command can prevent substantial repair work:

Make a copy of the sheet or preserve the original column if the data matters.
Check a sample of rows for exceptions, extra separators, and empty entries.
Confirm that the columns to the right are available for the output.
After splitting, compare a few results with the original text and check the last rows too.
Look for values such as postal codes or product IDs that must retain leading zeros.

Automatic interpretation can affect values that look like dates or numbers. Identifiers such as 0042 are not quantities, and losing their leading zeros can make them unusable. For sensitive data, preserve a copy and inspect the result before sharing or exporting it. If you accidentally split the wrong range, undo at once; if you have made more edits since then, restoring a copy may be safer than trying to reverse changes one by one.

Another important edge case is quoted text containing the delimiter. A simple split on commas may separate a comma that was meant to remain inside a field, such as a city-and-state phrase inside a quoted CSV value. When importing a properly structured CSV file, use Sheets’ import workflow and review how the file is parsed instead of assuming a direct text split will understand CSV quoting.

Which method should you choose?

Use Data > Split text to columns when you have a consistent delimiter and want to transform the existing cells once. Use SPLIT when you want the result to update with its source or need a repeatable formula for new rows. Use fixed-position extraction when the boundary is determined by character count, not punctuation. If formats vary significantly, clean or inspect the data before choosing any automatic method.

There is no separate charge for the standard split command or the SPLIT function within Google Sheets. The practical cost is the time spent checking the output and correcting exceptions. For a small, clean list, a menu split is efficient; for a frequently refreshed dataset, formulas may reduce repeated manual work, though they require room for their results and someone to maintain the sheet.

Frequently Asked Questions

Can Google Sheets split one cell into multiple columns?
Yes. Select the cell, choose Data > Split text to columns, and select the separator. The separated pieces appear in neighboring columns.

Can I split a column by a space?
Yes. Choose the space separator. This splits at every space, however, so a full name with a middle name can produce more than two parts.

Can I split text into rows instead of columns?
The Split text to columns command places results horizontally. To arrange pieces vertically, you will need another step, such as copying and transposing the output or using an appropriate formula-based layout.

Does splitting keep the original text?
The command changes the selected cells and places the pieces in neighboring columns; it does not automatically create a preserved original column. Copy the source first if you need to retain it.

Why does my split result show an error?
A formula result may be blocked by existing content in the cells where it needs to expand. Clear the output cells or move the formula to an empty area, then check that the source and delimiter are correct.

Is splitting text in Google Sheets the same as in Excel?
The goal is similar, but the menu options and formula functions differ. This article’s menu path and SPLIT examples are for Google Sheets in a web browser, not Excel.

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.