Excel Split Text in a Cell: Methods and Formulas

Formulas & functions · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, use Data > Text to Columns to split a cell’s contents into separate columns, or use =TEXTSPLIT(A2,", ") to split it with a formula. Text to Columns is useful for a one-time cleanup; TEXTSPLIT updates its results when the source text changes.

Choose a method for the job

The right method depends on whether you need a one-time rearrangement or a repeatable result. It also matters whether every record has the same separator and whether you want the output across columns or down rows.

  • Text to Columns: best for quickly converting a selected range, such as comma-separated names, into fixed columns. It changes the worksheet cells where you place the result.
  • TEXTSPLIT: best when you want a formula that recalculates as source data changes. It spills results into neighboring cells automatically.
  • TEXTBEFORE or TEXTAFTER: useful when you need only the text before or after a separator, rather than every part.
  • Power Query: useful for repeatable imports and larger data-cleaning jobs, especially when you can refresh the transformation after receiving updated files.

TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are available in Microsoft 365 and Excel 2024. If you use an older Excel version, Text to Columns is often the simplest built-in option; older-formula alternatives are described below.

Split text with Text to Columns

Text to Columns is a built-in conversion tool in desktop Excel. It reads the selected cells and places the separated pieces in columns. For example, it can turn Jordan Lee, Seattle, WA into three fields if commas mark the boundaries.

  1. Put the source text in one column and select the cells you want to split.
  2. Choose Data > Text to Columns.
  3. Select Delimited if a character such as a comma, tab, semicolon, or space marks each boundary. Choose Fixed width if the text uses consistent character positions instead.
  4. For Delimited, select the relevant delimiter in the wizard. If the separator is not listed, choose Other and enter it. Check the preview to see how Excel will divide the sample.
  5. Select a destination if you do not want the output to start over the selected cells, then finish the wizard.

Before finishing, check the columns to the right of the source. If those cells contain data, the split output can overwrite them. Choose an empty destination column or copy the source to a safe area first. Also inspect the preview: choosing space as a delimiter will split every space, so a two-word first name or street address may produce more columns than intended.

On Windows, the ribbon path is Data > Text to Columns. Excel for Mac also provides Text to Columns from the Data tab, although the wizard’s appearance and some labels can vary by release. In Excel for the web, available conversion controls may differ; a formula-based approach is often more convenient when TEXTSPLIT is available.

Use TEXTSPLIT for a formula-based result

TEXTSPLIT separates text at a specified delimiter and returns the pieces as a dynamic array. The results “spill” into neighboring cells, so the formula itself needs only one cell. The cells where results would appear must be empty, or Excel will show a spill error.

With the text Jordan Lee, Seattle, WA in A2, enter this formula in B2 to split at the comma followed by a space:

=TEXTSPLIT(A2,", ")

The result is Jordan Lee in B2, Seattle in C2, and WA in D2. The delimiter is the exact sequence comma-plus-space; if some records use a comma without a following space, the result may not split consistently. Choose the separator that actually appears in the source data.

TEXTSPLIT can also split one cell into rows. With line breaks within A2, use this formula in B2:

=TEXTSPLIT(A2,,CHAR(10))

In this layout, the second argument is omitted and CHAR(10) supplies the row delimiter. The pieces spill downward. This is handy for a cell containing a short list entered on separate lines, but make sure the cells below the formula are clear.

TEXTSPLIT also has options for repeated delimiters and empty pieces. With space-separated items in A2, this formula ignores empty results caused by consecutive spaces:

=TEXTSPLIT(A2," ",,TRUE)

The fourth argument, TRUE, tells Excel to ignore empty pieces. Without that setting, two consecutive spaces can create an empty result between words. Avoid ignoring empty pieces if blank positions themselves carry meaning in the original data.

Extract only the part you need

Sometimes splitting the entire value is unnecessary. For example, a product code such as AB-2048-blue might need only its prefix or final section. TEXTBEFORE and TEXTAFTER return text on one side of a delimiter, which can keep a worksheet simpler when you need just one component.

With Jordan Lee, Seattle, WA in A2, this formula in B2 returns the text before the first comma:

=TEXTBEFORE(A2,",")

It returns Jordan Lee. This formula in C2 returns everything after the first comma:

=TEXTAFTER(A2,",")

That result is Seattle, WA, including the space after the comma. If you need the final section after the last comma instead, TEXTAFTER has an instance-number option; for example, =TEXTAFTER(A2,",",-1) returns WA. These functions do not automatically clean spaces around the extracted text, so consider TRIM when the data has unwanted leading or trailing spaces.

Options for older Excel versions

If your version does not support TEXTSPLIT, TEXTBEFORE, or TEXTAFTER, Text to Columns remains a straightforward choice for splitting a range. For a formula that extracts one portion, older functions such as LEFT, MID, FIND, and LEN can work, but they require more care and usually assume a consistent delimiter.

With a value in A2 that has one comma separating two parts, this formula in B2 returns the text before the first comma:

=LEFT(A2,FIND(",",A2)-1)

For the same A2 layout, this formula in C2 returns the text after the comma and removes extra spaces at the ends:

=TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))

For example, if A2 contains Jordan Lee, Seattle, the results are Jordan Lee and Seattle. These formulas return only two portions, not a dynamic set of every field. They also return an error if the expected comma is missing, so they are best when the source format is dependable. In regional Excel settings, formula argument separators may display as semicolons rather than commas.

Prepare data and prevent common problems

Splitting does not necessarily make data clean or correctly typed. Text that looks like a date, number, or code may need additional attention after it is separated. For example, a postal code with a leading zero can lose that zero if Excel interprets the result as a number. In the Text to Columns wizard, review the data format options and choose Text for fields that must retain their exact characters. With formulas, format the destination appropriately and verify the displayed value.

  • Uneven separators: If some rows use commas and others use semicolons, a single delimiter may not split every row as intended. Standardize the source or handle the variations deliberately.
  • Spaces inside values: Splitting on every space can divide names, addresses, or descriptions into unintended pieces. Prefer a more specific delimiter, such as comma-space, if that matches the structure.
  • Extra spaces: Use TRIM on results when unwanted spaces remain around fields. TRIM removes excess ordinary spaces, but it does not fix every kind of invisible character.
  • Spill errors: A formula such as TEXTSPLIT needs a clear output area. Move existing values, merged cells, or other obstructions out of the spill range.
  • Missing delimiters: A row without the expected separator can behave differently from other rows. Check unusual records rather than assuming every line follows the same pattern.

Keep an unchanged copy of important source data before using a destructive conversion. Text to Columns can replace content in its destination cells, while formula results can be reviewed before you copy and paste them as values. If the split is part of a recurring process, record the steps or use a refreshable query rather than repeating manual edits without a check.

When Power Query is a better fit

Power Query is useful when text comes from a recurring file or when you need to apply the same cleanup to a table repeatedly. In desktop Excel, select a cell in the data and use Data > From Table/Range to open the Power Query Editor. Select the text column, then choose Home > Split Column > By Delimiter. Choose the delimiter and whether to split at each occurrence or at a specified position, review the preview, and load the result back to Excel.

Unlike a worksheet formula, a query does not update simply because you edit an unrelated cell; it applies its saved transformation when you refresh the query. This makes it useful for repeat imports, but it may be more setup than necessary for a one-off list. Keep the original data or query source available so you can correct the transformation if the file format changes.

Frequently Asked Questions

Can Excel split text in one cell automatically?
Yes. Enter TEXTSPLIT with the cell reference and delimiter, such as =TEXTSPLIT(A2,","). Excel returns the pieces in adjacent cells and recalculates them when the source changes, provided the spill area is clear.

How do I split a full name into first and last names?
If each name has exactly one space between the two parts, split on a space with Text to Columns or TEXTSPLIT. Names are not always two words, however, so this can misclassify middle names and compound surnames. For real contact lists, verify the output rather than assuming the first space always marks the boundary.

Can I split text into rows instead of columns?
Yes. TEXTSPLIT supports a row delimiter as its third argument. For line breaks within A2, =TEXTSPLIT(A2,,CHAR(10)) returns the parts down rows. In Text to Columns, the standard result is arranged across columns; transposing afterward is a separate step.

Why does TEXTSPLIT show a #SPILL! error?
Excel cannot place one or more results because the required output cells are blocked, often by existing content or merged cells. Clear the obstructing cells or move the formula to a larger empty area, then check that the expected spill range fits on the sheet.

Does Text to Columns change the original cell?
It writes split results into worksheet cells, which can replace existing content at the destination. Use a safe destination or keep a copy of the source data before completing the wizard. A TEXTSPLIT formula leaves its source cell intact, although its output still requires empty neighboring cells.

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.