Split Cells in Excel With Formulas: A Step-by-Step Guide

Formulas & functions · Updated November 20, 2026

To split text from one Excel cell into separate columns, enter =TEXTSPLIT(A2," ") in B2 when A2 contains text separated by spaces. Excel fills the results across adjacent cells. This works in Microsoft 365 and Excel 2024; if you have an older version, use the LEFT, MID, and FIND formulas below. Formulas separate a cell’s contents into neighboring cells—they do not divide one worksheet cell into smaller cells.

What You’ll Need

  • An Excel workbook with the text you want to separate, ideally one record per row.
  • A blank destination area beside the source data. TEXTSPLIT needs empty cells for its results to spill into.
  • Microsoft 365 or Excel 2024 for TEXTSPLIT. If you use an earlier version, you can use the older formula method in this guide.

Step-by-Step

  1. Identify the separator in your text. Look at a few cells and find the character or characters between the parts you want to extract. A space separates Maya Chen; a comma and space separate Chen, Maya; a hyphen might separate North-Region. The formula must use the same separator as the source. If the data is inconsistent, note the variations before choosing a formula.
  2. Choose an empty destination column. Suppose the source text is in A2:A100 and B2 onward is empty. Leave enough empty columns to the right of the formula for the widest result. For two-part names, reserve B and C; for addresses or product codes with more parts, leave additional columns. Keep the source column unchanged so you can check the results against the original.
  3. Enter TEXTSPLIT for a simple separator. With a full name in A2 and a regular space between the first and last names, enter this in B2: =TEXTSPLIT(A2," "). Press Enter. If A2 contains Maya Chen, B2 returns Maya and C2 returns Chen. The formula spills horizontally, so you do not need a separate formula in C2.
  4. Use a more forgiving space split if spacing varies. If cells might have leading, trailing, or repeated ordinary spaces, use this version in B2, assuming the text to split is in A2: =TEXTSPLIT(TRIM(A2)," ",,TRUE). For Maya Chen , the result is Maya in B2 and Chen in C2. TRIM removes extra ordinary spaces at the ends and reduces repeated spaces between words; TRUE tells TEXTSPLIT to ignore empty pieces caused by consecutive separators. This is useful for names, but do not use it if repeated spaces have meaning in your data.
  5. Match the delimiter for punctuation-separated text. With a value such as Chen, Maya in A2, and a comma followed by one space between the fields, enter =TEXTSPLIT(A2,", ") in B2. B2 returns Chen and C2 returns Maya. If the values use commas without spaces, use a comma as the delimiter instead; a mismatch can leave spaces at the start of a result or fail to split as expected.
  6. Copy the formula down the records. After confirming the result in row 2, select B2 and drag the fill handle—the small square at the lower-right corner of the selection—down the column. Excel adjusts A2 to A3, A4, and so on. Each row’s results spill across from its own formula. Alternatively, copy B2, select the destination cells below it, and paste. Do not copy a spilled result cell such as C2 as if it contained an independent formula.
  7. Use a legacy method if TEXTSPLIT is unavailable. For a single space between two parts, put the first formula in B2 and the second in C2. In both cases, the source text is in A2. The first formula extracts the text before the first space: =LEFT(A2,FIND(" ",A2)-1). The second extracts everything after that space: =MID(A2,FIND(" ",A2)+1,LEN(A2)). If A2 is Maya Chen, B2 returns Maya and C2 returns Chen. Fill both formulas down separately. The second formula returns the remainder, so for Maya Louise Chen, C2 returns Louise Chen, not just the last word.
  8. Check and save the result. Compare a sample of the split rows with the source, especially rows with extra spaces, missing parts, or punctuation. If you need fixed text rather than formulas that change when the source changes, select the results, copy them, then use Home > Paste > Paste Values. Keep the original source column until you are satisfied with the conversion.

In Excel for the web, TEXTSPLIT is available with Microsoft 365 and the formula is entered in the worksheet in the same way. Excel for Mac also supports these formulas in Microsoft 365 or Excel 2024; the formula behavior is the same, though ribbon layout and keyboard shortcuts can differ. If your Excel regional settings use semicolons as formula separators, Excel may require semicolons instead of the commas shown here.

Common Mistakes

  • Expecting one cell to become several cells. A formula returns results in neighboring cells; it cannot split the worksheet grid cell itself. Plan the destination columns before entering the formula.
  • Using the wrong delimiter. A formula looking for a space will not split text separated by a comma, tab, slash, or hyphen. Look closely at the source, including whether punctuation is followed by a space.
  • Leaving cells in the spill range occupied. TEXTSPLIT needs room to place every part. A value, formula, or merged cell in the way can stop the result from appearing.
  • Assuming every row has the same number of parts. Some records may have extra words or missing fields. TEXTSPLIT may return a different number of results for those records, so inspect the widest outputs and any gaps.
  • Deleting the source too soon. Formula results depend on their source values. If you delete or overwrite the source before converting the results to values, the outputs can change or become errors.

Tips

  • Start with a few representative rows, including the messiest examples, before filling a formula down hundreds of records.
  • Use the exact multi-character separator when the pattern is consistent. For example, splitting on comma plus space can keep the resulting fields cleaner than splitting on a comma alone.
  • Keep formulas in a separate output area or worksheet while checking them. This makes it easy to compare the split text with the original and redo the work if needed.
  • Convert results to values only when you want a static copy. Leave the formulas in place if the split results should update whenever the source text changes.
  • For a one-time operation, Excel’s Data > Text to Columns tool is another option, but formulas are useful when you want the split to update automatically or repeat for new rows.

Troubleshooting

You see #SPILL!. Select the error cell and check the area where the parts should appear. Clear any values or formulas blocking the spill, unmerge cells in that area if needed, and make sure the formula is not inside an Excel table column where dynamic-array spilling is restricted. Then re-enter or recalculate the formula.

The formula returns #NAME?. Your Excel version may not include TEXTSPLIT, or the function name may not be recognized in your installation. Use the LEFT, MID, and FIND method for a simple two-part split, or update to Microsoft 365 or Excel 2024. Check that the formula begins with an equals sign and that quotation marks around the delimiter are straight double quotes.

The old formula returns #VALUE!. FIND reports an error when it cannot find the separator. Check whether that row actually contains the expected space or whether it uses a different character, such as a nonbreaking space copied from a web page. The legacy formulas shown here also assume there is at least one separator in every source cell.

The output contains blanks or unwanted spaces. Check whether separators repeat or whether the source has spaces around punctuation. For ordinary repeated spaces, the TRIM version of TEXTSPLIT can help. If the underlying character is a tab or a nonbreaking space rather than a normal space, replace or clean that character first; TRIM does not remove every kind of whitespace.

The formula appears as text instead of calculating. Change the formula cell’s number format to General, then edit the cell and press Enter. Also check that there is no apostrophe before the equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent shortcut can depend on the keyboard layout, so check Formulas > Show Formulas.

Frequently Asked Questions

Can I split a cell into columns with one formula?
Yes. In Microsoft 365 or Excel 2024, TEXTSPLIT returns multiple parts from one formula and spills them into adjacent cells. It does not create smaller cells inside the original cell.

Does TEXTSPLIT work in older Excel versions?
TEXTSPLIT requires Microsoft 365 or Excel 2024. In earlier versions, use functions such as LEFT, MID, FIND, and LEN for a known separator and a predictable layout.

Can I split text using a comma or hyphen instead of a space?
Yes. Set the delimiter argument to the character or sequence actually present in the source, such as a comma, hyphen, or comma followed by a space. Check for inconsistent spacing before filling the formula down.

How do I split a full name into first and last names?
If every entry has exactly two parts separated by one space, TEXTSPLIT can return both parts in adjacent columns. For names with middle names, multiple surnames, or suffixes, decide how those cases should be handled; a simple split cannot reliably infer the intended name fields.

How do I keep the split results after deleting the original text?
Copy the formula results, then choose Home > Paste > Paste Values. Check the pasted values before removing the source, because ordinary formulas continue to refer to the original cells.

Can I split one cell’s contents into rows instead?
TEXTSPLIT can split using a row delimiter as well as a column delimiter, but the formula’s layout and spill direction differ. For the common task of separating fields into columns, use the column delimiter examples above and leave blank space to the right.

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.