Split Data in Google Sheets: Methods and Formulas

To split text into separate columns in Google Sheets, select the cells, then choose Data > Split text to columns and pick a separator. For a repeatable result that updates when the source changes, use the SPLIT function instead.

What splitting data in Sheets means

Splitting data means taking text stored in one cell and dividing it into parts that occupy separate cells, usually across a row. For example, a cell containing Mina Patel, Sales, West could become three cells: Mina Patel, Sales, and West. The separator between parts might be a comma, a tab, a space, or a character that you choose.

This is useful when information arrives combined in one field: names, addresses, product codes, tags, or values copied from a website. Separate columns are easier to sort, filter, summarize, and use in formulas. Splitting is not the same as moving data into multiple rows; the standard Split text to columns command places the pieces horizontally.

Before splitting, consider whether the separator is safe to use. A comma works well for a simple list, but it can also appear inside a company name or an address. If the separator occurs within a value, a basic split may divide that value incorrectly. Inspect a few source rows before applying a split to a large range.

Use Split text to columns for a one-time split

The built-in command is usually the quickest choice when you want to convert a selected range and do not need the result to update automatically. In Google Sheets in a web browser, follow these steps:

Select the cell or range containing the combined text. You can select a whole column if the entries are arranged consistently.
Choose Data > Split text to columns.
Use the Separator menu that appears near the selected data. Choose a suggested separator, such as comma, semicolon, period, or space, or select Custom and enter a character.
Review the results across the row. If the split is wrong, use the menu to choose another separator or undo the change.

Sheets may offer Detect automatically as a separator choice. It can be convenient for consistently formatted data, but do not assume it will infer the intended structure in every row. For example, a comma-separated list and a sentence containing commas can look similar to an automatic detector. A deliberate separator is more predictable when the format is known.

Make sure the cells to the right of the source are empty before splitting. The result needs room to expand, and existing values in neighboring cells can be affected or prevent you from safely using the output. For important data, duplicate the source column or make a copy of the sheet first. Once you have confirmed the split, you can retain the original column as a reference or remove it.

Use the SPLIT formula for a repeatable result

The SPLIT function is useful when source text may change or new entries will be added. It returns the parts in neighboring cells, so the formula can recalculate when its referenced cell changes. With the combined text Mina Patel, Sales, West in A2, enter this formula in B2:

With the source text in A2 and an empty output area beginning at B2, use =SPLIT(A2, ", ", FALSE, TRUE). For that example, the result is Mina Patel in B2, Sales in C2, and West in D2.

The second argument is the delimiter. The third argument, FALSE, tells Sheets to treat the delimiter , as one exact sequence rather than splitting at each character in it. The fourth argument, TRUE, tells the function to remove empty results. These optional settings matter when delimiters contain more than one character or when the source might have repeated separators.

If the values are separated by a plain comma with no following space, use a comma as the delimiter instead. For example, with red,green,blue in A2, the formula =SPLIT(A2, ",") returns red, green, and blue in adjacent cells. You can copy the formula down for additional rows. Leave enough empty columns for the widest expected result, because the output expands horizontally.

Choose and check the separator

A separator is the boundary that tells Sheets where one piece ends and the next begins. Common choices include:

Comma: Often used in simple lists or exported records, but may also be part of a name or address.
Space: Can separate words, but a full name such as Mina Patel would become two pieces. Repeated spaces can also create unexpected results depending on the method and settings.
Tab: Common in text copied from tables. A tab-separated line can contain fields that are already intended to become columns.
Custom character: A pipe (|), slash, or other marker can work well if it does not occur within the values.

Look at several examples, including the longest and messiest entries, before choosing. If every record follows the same structure, count the expected fields. For instance, a list of three product attributes should normally produce three columns. If some rows produce two and others five, the source may use inconsistent separators or include the separator within a value.

For a formula, remember that the delimiter is interpreted as text. A delimiter of comma followed by a space can distinguish Patel, Mina from a comma-only pattern. However, if some rows use comma-space and others use comma without a space, one exact delimiter will not handle both consistently. Standardize the source first or use a method designed for its actual format.

A careful workflow for real spreadsheets

A little preparation prevents common data-cleanup problems. For a one-time conversion, first make a copy of the source column or duplicate the sheet. Then test the split on a few representative rows rather than immediately applying it to every record. Include examples with blank fields, punctuation, extra spaces, or unusually long text.

After splitting, check whether each output column contains the kind of value you expected. Look for shifted fields, missing pieces, and results that spilled into columns containing other information. If the split created unwanted empty columns, review whether repeated delimiters or consecutive spaces were present. If you used a formula, verify that its output area is clear and that every row points to the intended source cell.

Once the result looks right, decide whether it should remain dynamic. A formula output can change when the source changes, which is helpful for a live data-cleaning process. For a fixed result, you can copy the output and use Edit > Paste special > Values only to replace formulas with their current values. Keep the original data until you have checked the finished table and saved any important changes.

Common problems and their fixes

The split creates too many columns: The chosen character may appear inside values, or the separator may be too broad. Try a more specific custom separator, such as comma followed by a space, and test it on representative rows.
A name breaks into separate words: Space was selected, but the name itself contains spaces. Use a comma or another character that marks the boundary between fields instead.
Some values appear blank or disappear: The source may contain consecutive delimiters, such as one,,three. With SPLIT, the optional setting for removing empty text controls whether empty pieces are kept; choose the setting that matches the data you need to preserve.
A formula reports an expansion problem: Cells to the right of the formula may not be empty. Clear or move the blocking data, then confirm that the output has enough columns.
Quoted commas are split anyway: A simple delimiter split does not understand that a comma inside quotation marks may belong to a field. For a properly formatted CSV file, use File > Import and review the import options rather than treating the file as ordinary comma-separated text.

One frequent misconception is that splitting automatically cleans or validates data. It only divides text according to a separator; it does not confirm that a date is valid, standardize spelling, or know which comma is structural. Treat the output as a transformation that needs a quick quality check, not as proof that the records are correct.

When to use a split, and when to use another method

Use Split text to columns when you need a quick, one-time conversion of consistently formatted values. Choose SPLIT when you want a visible, repeatable formula result that responds to changes in a source cell. If you receive a structured CSV file with quoted fields, import it so that the file format can be interpreted rather than splitting raw lines by a character.

Sometimes the goal is not to divide a cell but to extract one particular piece. For instance, if you only need the part before a known marker, extracting that part may be more appropriate than creating several columns. Likewise, if the data needs to be rearranged into rows, a horizontal split is not the right operation by itself. Think about the intended final table before choosing a tool.

Google Sheets works in a browser, so its menu names and formula options are not the same as Excel’s. Excel has its own Text to Columns feature and function set; instructions for Excel should not be substituted for the Sheets steps above. If a spreadsheet will be opened in both programs, check that any formulas and formatting behave as intended in each.

Frequently Asked Questions

Can Google Sheets split text into columns automatically?
Yes. Select the data and choose Data > Split text to columns, then choose Detect automatically or a specific separator. Review the output because automatic detection may not match the intended structure in every dataset.

How do I split a full name into first and last names?
If every name contains exactly two parts separated by one space, choose a space as the separator. If names can include middle names, multiple spaces, or compound surnames, a simple split will not reliably identify first and last names; inspect and handle those cases separately.

Can I split data into rows instead of columns?
The Split text to columns command and the SPLIT function produce horizontal pieces across columns. If you need one item per row, you will need an additional rearranging step after splitting or a different workflow suited to the source data.

Will a SPLIT formula update when the original cell changes?
Yes. A formula such as =SPLIT(A2, ",") recalculates when A2 changes. The output cells must remain available for the returned pieces, and filling the formula down creates separate results for the referenced rows.

How can I split comma-separated text without breaking commas inside quotes?
Do not rely on a basic split for quoted CSV fields. Import a properly formatted CSV using File > Import, then check the resulting columns and any import settings. Quoting rules are part of the CSV structure, not something a simple comma separator evaluates.

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.