Excel Split Cell Into Multiple Cells: Methods That Work

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cell or column and choose Data > Text to Columns to split its contents by a delimiter, such as a comma, or by fixed character widths. For a formula-based split that updates when the source changes, use TEXTSPLIT; the best method depends on whether you need a one-time cleanup, a repeatable result, or a split into rows rather than columns.

What splitting a cell means

A cell can contain several pieces of information even though Excel sees them as one text value. For example, A2 might contain Jordan Lee, West, 184. Splitting it can place the name, region, and number in separate cells across the same row. A delimiter is the character or text that marks each boundary: a comma, space, slash, tab, or other separator.

Splitting is different from moving a value or copying a cell. The result occupies neighboring cells, so those cells must be available. It is also different from splitting a worksheet or a merged cell. Excel cannot divide one ordinary cell into smaller cells while keeping the rest of the grid unchanged; it puts the pieces into existing cells in adjacent columns or rows.

Choose a method for your data

  • Text to Columns: Best for a one-time split of existing values. It offers delimiter and fixed-width options and works well for cleaning a pasted list.
  • TEXTSPLIT: Best when you want a formula result that recalculates as the source text changes. It is available in Microsoft 365 and Excel 2024.
  • Flash Fill: Useful when you want Excel to recognize a pattern from examples, such as extracting first names. Review its results because it infers the pattern rather than applying a defined delimiter rule.
  • Power Query: Useful for repeatable imports or larger datasets that you will refresh. It records transformation steps rather than requiring you to redo a manual split.

Before choosing, check whether the parts are consistently separated. A clean list with one comma between fields is straightforward. A list where commas also appear inside quoted addresses or descriptions needs more care, because a simple comma split may divide a field that should remain together.

Split with Text to Columns

Text to Columns is a practical choice when the source values are already in a worksheet and you want to replace them with separate fields. In the Windows desktop version of Excel for Microsoft 365, work through these steps:

  1. Make a copy of the source column or confirm that you can undo the change. Select the cells you want to split. If you select one cell in a continuous column, Excel can use the adjacent data to infer the range, but selecting the intended range explicitly is safer.
  2. Choose Data > Text to Columns. In the wizard, choose Delimited when a character separates the fields. Choose Fixed width when the fields start at consistent character positions, then select the break points shown in the preview.
  3. For a delimited split, select the separator used in your data, such as Tab, Comma, Semicolon, or Space. Choose Other to enter a different single character. Inspect the preview to confirm that the boundaries fall in the right places.
  4. If spaces separate fields, consider whether names or phrases contain internal spaces. The name “Jordan Lee” would become two fields if every space is treated as a delimiter. A comma between fields may be a more reliable separator for that data.
  5. Use the wizard’s text qualifier setting when the source has quoted fields, and check how consecutive delimiters should be treated. If empty fields matter, make sure the preview preserves them rather than silently collapsing the gap.
  6. On the final screen, set a destination if you do not want the output to begin in the selected column, and choose a column data format when needed. Select Finish only after confirming that the preview shows the expected number of columns.

For example, if A2 contains Jordan Lee,West,184 and you select Comma as the delimiter, Excel places the three fields in adjacent columns on that row. If the destination overlaps existing data, Excel can overwrite it. Put the output in a clear area or make a backup first. The Windows and Mac desktop versions both offer Text to Columns, though dialog layout and ribbon details can vary. In Excel for the web, availability and interface can depend on the current rollout; if the command is not shown, open the workbook in the desktop app.

Split with the TEXTSPLIT formula

TEXTSPLIT returns the separated pieces as an array that spills into neighboring cells. Unlike Text to Columns, the formula stays connected to the source: changing the source text updates the displayed pieces. It requires Microsoft 365 or Excel 2024; older perpetual versions may not recognize the function.

With the text Jordan Lee | West | 184 in A2, and with the cells to the right of B2 empty, enter this formula in B2:

=TEXTSPLIT(A2," | ")

It returns Jordan Lee, West, and 184 in B2, C2, and D2, respectively. The delimiter in the formula is the exact three-character sequence space, vertical bar, space. If the source instead uses a comma with no spaces, use a comma as the delimiter argument. Matching the actual separator matters; a delimiter that does not appear in the text will not create the intended divisions.

If a formula result does not appear, look for occupied cells in its spill area. Excel displays a spill error when something blocks the array. Clear the obstructing cells or move the formula. Also note that array results cannot be edited one piece at a time as if each were an independent formula; edit the source or the formula to change the output. To make a static copy, copy the results and use Paste Special > Values.

Split into rows instead of columns

Most people asking how to split a cell mean placing fields across columns, but sometimes each separated item should become its own row. TEXTSPLIT can do either: its second argument separates columns, while its third argument separates rows. The argument position is important. For example, putting a comma in the second argument spreads comma-separated items across columns; putting it in the third argument stacks the items vertically.

Text to Columns is designed to distribute fields into columns, not to turn one cell’s pieces into a vertical list. For a one-time row-based result, you can split into columns and then copy the results and use Paste Special > Transpose. For a recurring import, Power Query can split a column by delimiter and then use its split options to control how the pieces are arranged. Check the output before replacing the source, especially if order matters.

Use Flash Fill for pattern-based extraction

Flash Fill can fill a neighboring column based on examples you type. Suppose A2 contains Jordan Lee. Type Jordan in B2, begin entering the next expected first name in B3, and Excel may display a suggested pattern for the rest of the column. Choose the suggestion or use Data > Flash Fill. You can repeat the process in another column for last names.

This approach is handy when the intended result is based on a pattern rather than a simple separator. It is not a substitute for checking the data: names with middle initials, compound surnames, or inconsistent spacing may not follow one pattern. Flash Fill produces values rather than a live formula, so later edits to the original text do not automatically update the extracted results. If the preview is wrong or does not appear, provide more examples or use Text to Columns, a formula, or Power Query instead.

Use Power Query for repeatable splits

Power Query is a better fit when you repeatedly import or clean the same kind of file. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. If the range is not already an Excel table, confirm the range and whether it has headers. In the Power Query Editor, select the relevant column and choose Home > Split Column > By Delimiter or the corresponding command on the Transform tab. Choose the delimiter and the split option, inspect the preview, then select Close & Load.

Power Query keeps the transformation steps so you can refresh the query when the source data changes, rather than repeating every cleaning action manually. It can be especially useful when files arrive each week with the same structure. It does add setup and review time, so it may be excessive for a handful of cells. Power Query is also available in Microsoft 365 for Mac, but connector and feature support can differ by platform and version. Excel for the web has a more limited authoring experience; use desktop Excel if the required query tools are missing.

Prevent common splitting problems

  • Protect neighboring data. Splitting creates output in adjacent cells. Insert blank columns or choose a different destination before running a destructive operation.
  • Preserve leading zeros. A field such as postal code 00427 can become the number 427 if Excel interprets it numerically. Set the output column’s format to Text in the Text to Columns wizard, or format the destination as Text before pasting or importing.
  • Check dates and long numbers. Excel may interpret split pieces as dates or numbers and change how they display. Review the resulting values and choose a text format for identifiers that are not meant to be calculated.
  • Account for inconsistent separators. Some rows may use commas and others semicolons, or include extra spaces. Normalize the source or carefully configure the split; a single delimiter rule cannot reliably guess every irregular format.
  • Do not assume a space is a safe boundary. Splitting a full name at every space separates multiword first or last names. If the source has no dependable delimiter, use a reviewed pattern or clean the data manually.
  • Handle quotes and embedded punctuation. A comma inside a quoted description may belong to one field, not mark a boundary. Check the Text to Columns preview or use a proper import workflow for structured CSV data.

Keep the unsplit source until you have checked a sample from the beginning, middle, and end of the list. Compare the number of output fields, look for blanks where a value should exist, and verify that identifiers and dates retained their intended meaning. A visually tidy result is not necessarily a correct one if Excel has changed the underlying data type.

Frequently Asked Questions

Can Excel split one cell into two columns?
Yes. Use Data > Text to Columns and select the separator between the two pieces, or use TEXTSPLIT if you want a formula result. Ensure the cell to the right is empty or choose a safe destination.

Can I split text at a space but keep the original cell?
Yes. Use TEXTSPLIT in another cell or column so the source remains untouched. Text to Columns changes the selected data area, so copy the original first if you choose that method.

Why does Text to Columns overwrite my data?
Its output is placed in the selected range and cells to its right. If those cells contain information, the split can replace it. Move the source or set a different destination in the wizard before finishing.

Can I split a cell automatically when its contents change?
Yes. A TEXTSPLIT formula recalculates when its referenced text changes, provided the function is available in your Excel version and the spill area is clear. Text to Columns and Flash Fill produce static results instead.

How do I split a cell into separate rows?
TEXTSPLIT can use its row delimiter argument to return items vertically. For a one-time split with Text to Columns, split into columns first and transpose the results, then verify the order and blanks.

Does splitting a cell work in Excel for Mac?
Excel for Mac includes Text to Columns, and Microsoft 365 versions that support TEXTSPLIT can use the same formula. Menu layout and Power Query capabilities may differ from Windows, so check the command availability in your installed version.

Related

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.