Excel Split Text in a Cell: Methods and Formulas Guide

Formulas & functions · Updated November 21, 2026

Yes. In Microsoft Excel for Microsoft 365 on Windows, you can split text in a cell using Data > Text to Columns for a one-time split, or the TEXTSPLIT function for a formula-driven result that updates when the source changes. Flash Fill and Power Query are useful alternatives when the split follows a pattern or needs to be repeated.

Choose the right method

The best method depends on whether you need a fixed result or one that recalculates, and on how consistent your source text is. A delimiter is the character or sequence that marks a boundary, such as a comma, space, slash, or vertical bar. For example, the comma in Rina Patel, Support, Denver can separate the name, department, and city.

  • Text to Columns: A straightforward choice for splitting a selected range once. It writes the separated values into neighboring cells.
  • TEXTSPLIT: A formula option for a repeatable split. Its spilled results recalculate when the source cell changes.
  • Flash Fill: Useful when you can show Excel the pattern you want, such as extracting the first name from a list.
  • Power Query: Better for repeatable data-cleaning steps, especially when importing or refreshing larger tables.

Before you start, make sure there is room to the right or below the source cells. Most methods need empty cells for their results. If those cells already contain data, splitting can overwrite it or, with a formula, cause a spill error.

Split cells with Text to Columns

Text to Columns is the most direct built-in tool when each value uses a clear separator and you do not need the result to update automatically. It changes the selected cells into separate columns; it does not create a live link back to the original text.

  1. Select the cells containing the text. Select only the relevant source column if you do not want to affect other data.
  2. Choose Data > Text to Columns.
  3. In the wizard, choose Delimited if a character such as a comma separates fields. Choose Fixed width if the fields line up at consistent character positions.
  4. For Delimited, select the separator, such as Tab, Semicolon, Comma, Space, or Other. Use Other to enter a custom character.
  5. Check the preview. Set a destination if you want the output somewhere other than the cells beside the selection, then complete the wizard.

For example, if A2 contains Rina Patel,Support,Denver, choose Comma as the delimiter. Excel places the three parts in adjacent columns, beginning at the destination. If the source has spaces after commas, the resulting second and third values may begin with a space; inspect the preview and clean up the result if needed. A space delimiter can also split a full name into more than two pieces when names contain middle names.

Take care with the destination. If you accept the default and neighboring cells contain information, the split output can replace that information. For a cautious workflow, insert blank columns first or choose an empty destination. On Mac, the command is also available from the Data tab, though the wizard’s appearance can differ by Excel release. Excel for the web may offer a different interface or fewer options than the Windows desktop wizard.

Split with the TEXTSPLIT function

TEXTSPLIT is available in Microsoft 365 and Excel 2024. It divides text using a column delimiter, a row delimiter, or both. The result spills into neighboring cells, so those cells must be empty. Unlike Text to Columns, the formula keeps the result connected to its source.

With the text Rina Patel,Support,Denver in A2 and a comma between each field, enter this in B2:

With the source text in A2, this formula splits at each comma and returns Rina Patel, Support, and Denver across the row:

=TEXTSPLIT(A2,",")

In Excel, the first argument is the text to split and the second is the column delimiter. The three returned values occupy B2, C2, and D2. If A2 changes to another comma-separated record, the spilled values update automatically. Enter the formula in just the first output cell; do not copy it across the row.

To split on a multi-character separator, provide the whole separator as the delimiter. With Rina Patel | Support | Denver in A2, this formula returns the three fields across columns:

=TEXTSPLIT(A2," | ")

Spaces are part of that delimiter, so it removes the spaces surrounding the vertical bars as well. Alternatively, splitting only on the bar would leave spaces at the edges of the returned values. For data with repeated delimiters, the optional ignore_empty argument can skip empty fields. With North,,West in A2, this formula returns North and West, omitting the empty middle field:

=TEXTSPLIT(A2,",",,TRUE)

The third argument is left blank because no row delimiter is needed; TRUE is the fourth argument, which tells Excel to ignore empty results. If empty fields carry meaning in your data, do not ignore them: for example, North,,West may indicate that the middle value is missing, and keeping that blank preserves the positions of the other fields.

Split text at one boundary

Sometimes you do not want to divide a cell at every separator. If you need only the text before or after the first space, use TEXTBEFORE or TEXTAFTER, available in Microsoft 365 and Excel 2024. These functions are handy for separating a first name from the rest of a name, or a product code from its description.

With Jordan Lee in A2, this formula returns Jordan:

=TEXTBEFORE(A2," ")

With the same value in A2, this formula returns Lee:

=TEXTAFTER(A2," ")

These examples assume one space between the first and remaining name. If names have inconsistent spacing, extra spaces, or multiple given names, the result may not represent a person’s first and last name as intended. Clean or review the source data rather than assuming that every name follows the same structure. For older Excel versions without these functions, Text to Columns can handle a one-time split; formulas built from older functions are also possible but need more care around missing delimiters.

Use Flash Fill for a recognizable pattern

Flash Fill can infer a pattern from examples you type. It is useful when the source does not have a convenient delimiter, or when you want to extract a particular part rather than split every field. For instance, a list of full names might include middle names, but your goal is to create a column containing only the first word.

  1. Keep the original values in one column and type the desired result for the first row in the next empty column.
  2. Start typing the result for the next row. If Excel recognizes the pattern and displays a preview, press Enter to accept it.
  3. If no preview appears, select the output cells and choose Data > Flash Fill, or press Ctrl+E on Windows.

Flash Fill produces values, not a formula that recalculates when the original text changes. It can also misread irregular examples: a pattern demonstrated with two simple names may not handle initials, compound surnames, or inconsistent punctuation as expected. Review the results, especially when the output will be used for reporting or records. On Mac, the menu command is available in supported desktop versions; keyboard shortcut behavior can vary, so use the Data tab if Ctrl+E is not the right shortcut on your setup.

Split and refresh data with Power Query

Power Query is designed for importing and transforming data through a sequence of steps. It is a strong choice when you receive the same kind of file repeatedly and want to apply the same split whenever the source is refreshed. For example, you can import a table, split a column by a comma, and load the transformed result to a worksheet.

In Excel for Microsoft 365 on Windows, select a cell in a source table or range and use Data > From Table/Range to open the Power Query Editor. Select the column, then choose Home > Split Column > By Delimiter. Pick the delimiter and whether to split at each occurrence or at a specified occurrence, review the preview, and use Home > Close & Load to return the result to Excel. Exact options can vary with the selected data and Excel release.

Power Query keeps the transformation steps so you can refresh the query when the input data changes, but it does not update the worksheet output after every edit in the way a cell formula does. Refresh the query to apply the saved steps again. Its split options can also handle more involved transformations than a single formula, although a simple two-field task may be faster with Text to Columns or TEXTSPLIT. Power Query is available in Excel for Windows and Mac with version-dependent features; its editing experience in Excel for the web differs.

Common problems and ways to avoid them

  • The formula shows #SPILL!: One or more cells where the result needs to go are occupied, merged, or otherwise blocking the spill range. Clear the cells or move the formula to an open area.
  • The split creates too many or too few parts: Check whether the chosen delimiter occurs inside the actual values. Splitting on spaces, for example, divides every word, not just first and last names.
  • Spaces remain around a field: Use a delimiter that includes the surrounding spaces, or clean the output after the split. A formula such as TEXTSPLIT(A2,", ") assumes that every comma is followed by exactly one space.
  • Leading zeros disappear or values change format: Excel may interpret a result as a number, date, or other recognizable value. In Text to Columns, set the affected output column’s format to Text in the wizard when preserving the original characters matters. Check imported results before using them.
  • Some rows use a different separator: A single delimiter rule may not work consistently. Standardize the source first, use a more flexible Power Query transformation, or handle exceptions separately.
  • Commas inside quoted text split unexpectedly: A simple TEXTSPLIT formula treats each delimiter as a boundary; it does not parse every CSV quoting rule. For structured CSV files with quoted fields, import the file through Excel’s data import or Power Query tools rather than assuming that a basic split will respect quotes.

It is also worth distinguishing splitting from unmerging. If a worksheet cell visually spans multiple columns because it was merged, Text to Columns will not separate it into the original values. The data must first exist as text with a delimiter or another recognizable pattern.

Frequently Asked Questions

Can Excel split text in one cell into rows instead of columns?
Yes. In Microsoft 365 or Excel 2024, use TEXTSPLIT with a row delimiter as its third argument. For example, with Red;Blue;Green in A2, =TEXTSPLIT(A2,,";") returns the items vertically. Leave the column-delimiter argument blank because this example uses a row delimiter.

Can I split a cell by a space?
Yes. Choose Space in the Text to Columns wizard or use a space as the TEXTSPLIT delimiter. Keep in mind that every space becomes a boundary, so a phrase with four words will usually produce four pieces, not just a first and last name.

Will the split update when I edit the original cell?
A TEXTSPLIT or other formula result recalculates when its source changes. Text to Columns and Flash Fill create values that do not automatically recalculate. Power Query output changes when you refresh the query.

Can I split text without a delimiter?
Yes, if there is another dependable pattern. Text to Columns offers Fixed width, and Flash Fill can infer a pattern from examples. If the text has no consistent delimiter or structure, Excel cannot reliably know where you intend to divide it, so you may need to add separators or correct the data manually.

Why does TEXTSPLIT not work in my Excel?
It may be unavailable in an older Excel version; TEXTSPLIT requires Microsoft 365 or Excel 2024. Also check the formula’s delimiter and ensure the spill destination is clear. In some regional settings, Excel uses semicolons rather than commas between formula arguments, even though the formulas shown here use standard comma separators.

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.