Excel Split Cell Text Into Separate Cells: A Practical Guide

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns to split text using a delimiter such as a comma or space; for a formula-based result that updates when the source changes, use =TEXTSPLIT(A2,",") in a blank cell. Choose Text to Columns for a one-time conversion, and TEXTSPLIT when you want the result to recalculate automatically.

How Excel splitting works

Splitting cell text means taking one value and placing its parts into neighboring cells. For example, a cell containing Jordan Lee,Seattle,WA can become three cells: Jordan Lee, Seattle, and WA. The original value is not inherently divided into separate records; Excel identifies separators or positions and distributes the pieces across columns or rows.

The separator is often called a delimiter. Commas, tabs, semicolons, spaces, slashes, and hyphens are common delimiters, but the right choice depends on the data. In Lee, Jordan, the comma marks the boundary between last and first name. In lee.jordan@example.com, splitting at every period would probably damage the address, so a delimiter should be chosen to reflect the structure you actually need.

Before splitting, check what should happen to the source. Text to Columns writes the result into cells and can replace content in adjacent columns. A formula normally leaves the source intact and spills its result elsewhere. If the original data matters, keep a copy or send the output to an empty area.

Choose the right method

For a quick, one-off split, Text to Columns is usually the clearest option. It works on selected cells and offers a preview before you finish. For a repeatable workflow, TEXTSPLIT is more suitable because its output can update when the source cell changes. Power Query is useful when you regularly import and clean larger datasets, particularly when the same steps need to be repeated after refreshing new data.

  • Text to Columns: best for manually splitting existing values once, with a delimiter or fixed character positions.
  • TEXTSPLIT: best for formula-driven output in Microsoft 365 or Excel 2024.
  • Power Query: best for repeatable transformations on imported or larger tables.
  • Flash Fill: useful for recognizing a simple pattern from examples, but less dependable than a clearly defined delimiter when the data varies.

Do not treat “split text” and “split a worksheet” as the same task. Text to Columns divides a cell’s contents into fields. It does not split a workbook into separate files, nor does it automatically put each resulting value on a different worksheet.

Split text with Text to Columns

Text to Columns is available in the desktop Excel application. For a comma-separated list in A2:A100, use these steps:

  1. Select the cells containing the text. Selecting only the filled source column makes it easier to avoid changing unrelated data.
  2. Open Data > Text to Columns.
  3. Choose Delimited if a character such as a comma or tab separates the fields. Choose Fixed width if the parts occupy consistent character positions.
  4. For Delimited, select the appropriate delimiter in the wizard. If the separator is not listed, choose Other and enter its character. Check the preview to confirm how Excel will divide each row.
  5. Choose a destination if you do not want the results to start in the original column, then select Finish.

For example, if A2 contains Jordan Lee,Seattle,WA and you select comma as the delimiter, Excel places Jordan Lee in the first output column, Seattle in the next, and WA in the third. If you leave the destination at its default, the first result begins in the source column. Existing values to the right can be overwritten, so inspect the destination area before finishing or specify an empty destination cell in the wizard.

The Fixed width option is appropriate for consistently positioned text, such as a legacy report where the first 10 characters are an ID and the next 20 are a description. It is not a good fit when names or descriptions have varying lengths. In the wizard preview, click to add a break line, or drag an existing break line to change where Excel divides the text.

On Excel for Mac, Text to Columns is also accessed from the Data tab, though the precise layout can differ by release. Excel for the web has a more limited interface and may not expose the same wizard in every version or account; use the desktop app if you cannot find the command. The steps above refer to Microsoft 365 for Windows.

Use TEXTSPLIT for a dynamic result

TEXTSPLIT is available in Microsoft 365 and Excel 2024. It returns a dynamic array, meaning its pieces spill into adjacent cells automatically. With the comma-separated text in A2, enter this formula in B2:

=TEXTSPLIT(A2,",")

If A2 contains Jordan Lee,Seattle,WA, the formula returns Jordan Lee in B2, Seattle in C2, and WA in D2. Change the source text and the formula result updates. Leave the spill area clear: if another value blocks one of the output cells, Excel displays a spill error rather than overwriting that value.

TEXTSPLIT can also split into rows by supplying a row delimiter. With A2 containing red|blue|green, this formula in B2 places the three items vertically:

=TEXTSPLIT(A2,,"|")

The second argument is left empty because there is no column delimiter; the third argument specifies the row delimiter. This is useful when a list should be arranged down a column rather than across a row. If a string contains consecutive delimiters, such as red||green, the default behavior can produce an empty field between them. TEXTSPLIT includes optional arguments for ignoring empty fields and handling matching, but use them only when their effect is appropriate for the data.

To handle commas and semicolons in the same source, normalize the semicolons to commas first. With the text in A2, enter this in B2:

=TEXTSPLIT(SUBSTITUTE(A2,";",","),",")

For an input of Jordan Lee;Seattle,WA, the result is Jordan Lee, Seattle, and WA across three cells. This approach works when those two characters have the same meaning as separators. If punctuation is meaningful within a field, replacing it can create an incorrect split.

Split names, addresses, and uneven data

Real-world text is not always as regular as a clean comma-separated example. A full name such as Jordan Lee can be split on a space, but a person with a middle name or a two-part surname may produce more pieces than expected. If the goal is first name versus everything after it, splitting every space is not necessarily the right operation. Check the range of values and define the desired fields before choosing a delimiter.

Addresses raise similar issues. A comma-separated address may contain commas inside a business name or a street line. In properly formatted CSV data, quotation marks can protect a field that itself contains a comma, but a simple delimiter split may not interpret every import format as intended. When importing a file, use Excel’s import tools or Power Query and review the preview, especially if fields are quoted or contain inconsistent punctuation.

Uneven rows may produce a different number of pieces. For example, one row might contain two comma-separated values and another four. TEXTSPLIT can return arrays of different widths for different source cells, so placing a separate formula on every row may create overlapping spill results. In that situation, consider Text to Columns for a selected range, or use Power Query to split a whole column into a consistent table and decide how missing fields should be represented.

For a simple repeated pattern, Flash Fill may infer the output from examples. Enter the desired result beside the original data, begin typing the next example, and use Data > Flash Fill if Excel offers the pattern. Flash Fill creates values rather than a formula-driven relationship, so later edits to the source are not automatically reflected. Review the results instead of assuming that the inferred pattern handles exceptions.

Check the output and prevent common problems

A successful split is not necessarily a correct one. Verify representative rows, including the shortest and longest entries and any rows with unusual punctuation. A delimiter that appears inside a field can create extra columns, while a missing delimiter can leave two values together. If the preview or output looks wrong, undo the operation promptly and adjust the delimiter or transformation.

  • Protect adjacent data: Text to Columns can write across neighboring columns. Move the output or insert blank columns first if necessary.
  • Preserve leading zeros: A value such as 00427 may be interpreted as a number and displayed as 427. In the Text to Columns wizard, use the data preview and set the relevant output column’s format to Text when the zeros are part of an identifier.
  • Watch for extra spaces: A delimiter followed by a space can leave a leading space in the next field. Review the result and clean it if required; a formula such as =TRIM(TEXTSPLIT(A2,",")) can remove ordinary leading and trailing spaces in a TEXTSPLIT result.
  • Do not split dates casually: Dates can be stored as numeric values with date formatting. Splitting their displayed appearance may not produce reliable date fields. Use a date-aware transformation if the goal is to extract month, day, or year.
  • Check number and date interpretation: Decimal and date conventions differ by region. A text-to-columns operation or import can interpret the same characters differently under different regional settings.

For formulas, remember that the output occupies a spill range. Do not type into those cells while the formula is active. If you need fixed values rather than a live relationship, copy the results and use Home > Paste > Paste Values in a clear destination. Keep the original column until the results have been checked.

When Power Query is a better choice

Power Query can split a column as part of a repeatable data-cleaning process. In desktop Excel for Microsoft 365 on Windows, select a cell in a table or source range and choose Data > From Table/Range. In the Power Query Editor, select the column, then choose Home > Split Column > By Delimiter (the command can also be available on the Transform tab). Choose the delimiter and the desired split behavior, inspect the preview, and select Close & Load to return the transformed data to Excel.

Unlike Text to Columns, Power Query records transformation steps. When the source data is replaced or extended in a connected workflow, refreshing the query can repeat those steps. It is especially helpful for recurring imports, multiple cleanup operations, and datasets too large or messy for a quick manual conversion. It may be excessive for splitting three cells once, and the query output is not edited like ordinary source data: make changes to the source or query steps and refresh.

Power Query’s exact options can vary with the selected delimiter and Excel release. Always check how it treats each occurrence, consecutive separators, and quoted content. If a file uses a complex text format, importing it with the correct encoding and delimiter settings is safer than pasting it into cells and guessing how to divide it afterward.

Frequently Asked Questions

Can Excel split one cell into rows instead of columns?
Yes. In Microsoft 365 or Excel 2024, TEXTSPLIT can use its row-delimiter argument, as in =TEXTSPLIT(A2,,"|"). Text to Columns distributes split fields horizontally; Power Query can also transform data when you need a repeatable row-oriented result.

Can I split text by a space?
Yes. In Text to Columns, select Delimited and choose Space. With TEXTSPLIT, use =TEXTSPLIT(A2," "). If there are multiple spaces or spaces inside names and phrases, inspect the output carefully because each space may create another field.

How do I split a cell at the first comma only?
Text to Columns splits at each occurrence of the chosen delimiter, so it is not the best choice if later commas must stay in the second field. In Microsoft 365 or Excel 2024, use =TEXTBEFORE(A2,",") for the text before the first comma and =TEXTAFTER(A2,",") for the text after it. These functions are available in those versions, not in every older Excel release.

Why does TEXTSPLIT show a spill error?
One or more cells needed for the result are not empty, or the formula is inside a location where the array cannot spill as expected. Clear the blocked cells or move the formula to an open area. The formula does not erase existing content to make room.

Will splitting change the original cell?
Text to Columns writes the split fields into the worksheet and may replace the source value in the first output cell. TEXTSPLIT returns results elsewhere while the formula remains in its own cell. For either method, retain a backup if the original text may be needed later.

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.