Split Cells in Excel: Text to Columns and Formulas

Formulas & functions · Updated November 18, 2026

To split the contents of a cell in Excel, select the cells, then choose Data > Text to Columns, pick Delimited or Fixed width, and follow the wizard. If you mean dividing one worksheet cell into two smaller cells, Excel cannot do that: you can split its contents into neighboring cells, or unmerge a cell that was previously merged.

What “split a cell” means in Excel

Excel’s grid is built from rows and columns, so an individual cell cannot be cut into smaller, independently addressable cells. The phrase “split cell” usually means separating text or values stored together, such as turning “Mina Patel” into first-name and last-name columns, or dividing “Seattle, WA” into city and state.

That distinction matters because the available solution depends on the result you want. Text to Columns and formulas divide a cell’s contents across adjacent cells. Unmerge separates a previously merged area back into its original grid cells, but it does not divide the text that was in the merged cell. In a table, splitting a value also creates more structured data that is easier to sort, filter, validate, and use in formulas.

Split data with Text to Columns

Text to Columns is a good choice when you have a range of existing values and want to separate them in place. It works on a selected column and can use a character such as a comma, a space, or a tab as a separator. On Excel for Microsoft 365 for Windows, use these steps:

  1. Select the cells containing the combined values. Include only the data you intend to split.
  2. Open Data > Text to Columns.
  3. Choose Delimited when a character separates the parts. Choose Fixed width when the split positions are based on consistent character locations. Select Next.
  4. For a delimited split, check the relevant option, such as Comma, Space, or Tab. Use Other to enter a different delimiter. Check the preview to confirm the result.
  5. Choose Next, then review the column data format if needed. Select Text to preserve values such as postal codes or product IDs that begin with zero.
  6. Set Destination to the top-left cell where the output should go, then select Finish.

For example, if A2 contains Seattle,WA and you choose a comma delimiter, Excel places Seattle in the first output column and WA in the next. If the destination is left at the original cell, the split output can overwrite cells to its right. Choose a blank area when you need to retain the original values as a backup.

Choose the right delimiter or fixed width

A delimiter is the character or mark that signals a boundary between pieces of data. Commas commonly separate fields in imported data, spaces can divide names, and tabs often separate copied columns. If the delimiter appears inside a value as ordinary text, a simple split may create too many columns. Check the preview rather than assuming that every comma or space is a true boundary.

  • Comma: useful for entries such as Seattle,WA, provided commas are not also part of a field.
  • Space: useful for a simple two-part value, but a full name such as Jordan Lee Kim may produce three parts rather than a first and last name.
  • Other: useful for separators such as a pipe character in Order|Date|Status.
  • Fixed width: useful when each part occupies a consistent position, as in records padded to the same character columns. Click in the preview to add a break, or drag a break to adjust it.

In a delimited file, a text qualifier can protect a delimiter that belongs inside a field. For example, a comma inside a quoted field may be data rather than a column boundary. If your preview breaks such a field incorrectly, check the wizard’s text qualifier setting and confirm the source format before finishing.

Use Flash Fill for pattern-based splits

Flash Fill can recognize a pattern from examples you type. Suppose A2 contains Mina Patel. Type Mina in B2, then start entering Patel in C2. Excel may show a preview for the remaining rows; accept it if the suggestions are correct. On Windows, you can also select the target cells and press Ctrl+E to run Flash Fill. The command is available from Data > Flash Fill as well.

This method is convenient when the desired result is easy to demonstrate but the separator rules are irregular. However, Flash Fill returns values based on the pattern it detects; it is not a live formula linked to the source. If the original text changes later, you may need to run Flash Fill again. Always inspect the output for exceptions, especially with middle names, initials, suffixes, inconsistent spacing, or entries that do not follow the examples. On Mac, look for Data > Flash Fill; the keyboard shortcut can differ by keyboard and Excel version.

Split a cell with a formula

A formula is useful when the source data may change and you want the separated results to update automatically. In Microsoft 365 or Excel 2024, the TEXTSPLIT function can split text using a delimiter. With the combined values in A2, enter this formula in B2 to split at each comma:

=TEXTSPLIT(A2,",")

For example, if A2 contains Seattle,WA, the formula returns Seattle in B2 and WA in C2. The results spill into neighboring cells, so keep those cells empty. If Excel reports a spill error, check whether anything is blocking the output area. Copy the formula down to handle more source rows; each formula will split the value on its own row.

To split a name on spaces, the same approach works only when spaces reliably mark the desired boundaries. A name with multiple spaces may spill into more columns than expected, and a name with a middle name will not automatically determine which two words count as first and last name. Decide the data rule first, then choose the delimiter or a more specific formula strategy. TEXTSPLIT is not available in every older Excel release; if you do not have it, use Text to Columns, Flash Fill, or formulas supported by your version.

Prevent overwritten data and unwanted conversions

Text to Columns writes results into cells, so the destination is an important safeguard. If the output needs two columns but the cells to the right already contain information, Excel may replace that information. Before splitting, either insert empty columns, choose a separate destination, or save a copy of the workbook. This is particularly important when working with imported reports that contain data in neighboring columns.

Also check how Excel interprets the resulting values. A product code such as 00418 may lose its leading zeros if treated as a number. Choose the Text column format in the wizard when the characters must remain exactly as written. Dates can also be interpreted according to regional settings, so review date-like values before relying on them. If the entries are identifiers rather than quantities, storing them as text is often the safer choice because arithmetic on them is not meaningful.

Common problems and practical fixes

  • Everything splits into too many pieces: the delimiter occurs inside some values, or a space-based split encounters middle names or extra spaces. Review the preview, use a more appropriate delimiter, or use Flash Fill for a carefully checked pattern.
  • Parts appear in the wrong columns: confirm the destination and look for existing values in the output area. Undo immediately with Ctrl+Z on Windows if the result is wrong and the action is still undoable.
  • Leading zeros disappear: set the affected output column to Text in the wizard. Changing a number format after zeros have been removed may not restore the original digits.
  • A formula shows a spill error: clear the cells where the formula needs to return its results, and check for merged cells in that area.
  • Rows do not follow the same pattern: do not accept Flash Fill suggestions without checking exceptions. One pattern may not correctly handle every record.

For a non-destructive workflow, copy the source column to a new area before splitting. Keep the original until you have checked the number of output fields, several unusual records, and the values at the bottom of the range. A quick review of a few typical rows is not enough if the source contains multiple formats.

Excel desktop, Mac, and web differences

The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also has Text to Columns and Flash Fill, but the ribbon layout and keyboard shortcuts can vary; use the Data tab and choose the matching command if the Windows shortcut does not apply. On Mac, the common undo shortcut is Command+Z, while Windows uses Ctrl+Z.

Excel for the web can have a different ribbon layout or feature availability from the desktop app. If Text to Columns is not available in your web session, open the workbook in the desktop app or use a supported formula such as TEXTSPLIT if your Excel version provides it. Regardless of platform, make sure the output cells are empty and check the results before deleting the original combined values.

Frequently Asked Questions

Can I split one Excel cell into two separate cells?
Not literally. Excel cannot divide one grid cell into two smaller cells. You can split the text into adjacent cells with Text to Columns, Flash Fill, or a formula. If the cell is part of a merged range, use Home > Merge & Center > Unmerge Cells to restore the underlying cells.

How do I split a full name into first and last names?
For consistently formatted two-part names, select the column and use Data > Text to Columns with Space as the delimiter. Check the preview first: middle names, suffixes, and double-barrelled names can create extra parts or an incorrect result.

Can Excel split text into rows instead of columns?
Text to Columns is designed to put separated fields into columns. In Microsoft 365 or Excel 2024, TEXTSPLIT can return results across columns or down rows when supplied with the appropriate row delimiter. Check the function’s arguments and leave enough empty cells for the results to spill.

Why did Text to Columns replace data next to my selection?
The output was written into neighboring cells that already contained information. Undo the operation if possible, then run it again with a blank destination or move the existing data first. A backup copy is useful before performing a large split.

Does Text to Columns update automatically when the source changes?
No. It writes split values into cells as a one-time operation. Use a formula when the output should recalculate after the source changes, or rerun the command after editing the original data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.