How to Split Text in Excel: A Step-by-Step Guide

Updated 5 November 2026 · Standard formula, shown below

Splitting text in Excel separates information stored in one cell—such as a full name, address, or comma-separated list—into individual cells. You can use Text to Columns for a quick one-time split, or formulas when you want results that update with the original text. Before you begin, make a copy of your data and check that the cells to the right of the source column are empty.

What You’ll Need

An Excel workbook containing the text you want to split.
A clear idea of what separates the pieces: a comma, space, hyphen, tab, or another character.
Empty columns for the results, or a plan to place formula results somewhere separate.
For formula options such as TEXTSPLIT, a recent Excel version that includes that function. If it is unavailable, use Text to Columns or the alternatives described below.

Step-by-Step

Prepare the data. Save a copy of the workbook or duplicate the source column. Splitting can overwrite cells beside the selected data. If the first row contains a heading, keep it in mind when selecting the range; do not treat it as a data value unless you want to split it too.
Choose the separator. Look at a few cells and identify the character between the parts. For example, Riley Chen uses a space, Chen, Riley uses a comma, and North-Region uses a hyphen. Confirm the pattern is consistent. A space-based split may not work for full names that include middle names or multi-word surnames.
Use Text to Columns for a quick split. Select the cells in the source column, then open the Data tab and choose Text to Columns. In the wizard, select Delimited if a character separates the pieces, then choose Next. (Choose Fixed width only when the text has consistent positions rather than a separator.)
Set the delimiter. On the delimiter screen, check the separator that matches your data: Tab, Semicolon, Comma, or Space. Choose Other to enter a character such as a pipe (|) or hyphen. The preview shows how Excel will divide the text. If the separator can appear inside a value, inspect the preview carefully; Text to Columns may split there too.
Choose where the results go. Select Next, then set the destination field if you do not want the split values to replace the original column. Pick an empty starting cell with enough blank columns to its right. Review the preview and any column data-format options, especially if a piece resembles a date or a number with leading zeros. Click Finish when the preview is correct.
Check the output. Compare several results with the original text, including rows with the longest values and any blanks. Make sure nothing important was overwritten and that leading zeros, dates, and punctuation remain as intended. Keep the untouched source column until you are confident the split is correct.
Use a formula for a repeatable split (optional). In a blank cell, enter =TEXTSPLIT(A2," ") to split the contents of A2 at each space, or replace the space with the delimiter your data uses, such as a comma. Press Enter. In versions with dynamic arrays, the results spill into adjacent cells automatically. Fill the formula down for other rows if Excel does not do so automatically. Leave the spill area empty.
Split only selected parts with formulas (optional). If a cell contains a first and last name separated by one space, use =TEXTBEFORE(A2," ") for the text before the first space and =TEXTAFTER(A2," ") for the text after it. These functions are useful when you need just two portions, but the example assumes there is only one relevant space. Adjust the delimiter or formula if the structure differs.

Common Mistakes

Leaving the destination set to the source column: Text to Columns can replace the original contents. Choose a separate empty destination or work on a copy.
Picking the wrong delimiter: A comma may be followed by a space, leaving unwanted spaces at the start of a result. Check the preview and consider whether the space should also be handled.
Splitting inconsistent data as if it were uniform: A name with a middle initial or an address with a unit number may produce a different number of pieces. Review exceptions before applying one rule to every row.
Changing number-like text unintentionally: Excel may interpret results as dates or numbers, and leading zeros can disappear. Use the column data-format controls in the wizard or format the destination as Text before splitting.
Expecting a formula to work in every Excel version: TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are not available in some older releases. Use Text to Columns or compatible older functions if Excel reports an unrecognized function.

Tips

Use Text to Columns when you need a straightforward, one-time change. Use formulas when the source values may change and you want the split results to update.
For a separator that is more than one character, TEXTSPLIT can use a text string as its delimiter, for example =TEXTSPLIT(A2," - "). Match the spaces around the hyphen if they are part of the pattern.
If a delimiter appears more than once, Text to Columns and TEXTSPLIT generally create additional output columns for each occurrence. Ensure there is enough room and check whether every split is useful.
To turn formula results into fixed values, copy the results and use Paste Special > Values. Do this only after checking the results; it removes the formulas’ link to the source.
When a dataset is imported regularly or needs repeatable cleanup, Power Query can split a column by a delimiter and repeat the transformation when the data is refreshed.

Troubleshooting

Text to Columns is unavailable or the wizard does not appear: Confirm you have selected cells in a worksheet column and look under the Data tab. If your data is inside an Excel table or a protected sheet, try working on a copied range in a regular worksheet, provided you have permission to edit it.

The results appear in one column: Return to the wizard and check that you selected Delimited and the correct delimiter. The preview should show the intended divisions before you finish. If the separator is a special or unusual character, enter it under Other.

Some rows split differently: Compare the affected rows for extra separators, missing separators, or spaces. If the data is irregular, a single Text to Columns operation may not be suitable. Clean or standardize the source first, or use formulas designed around the specific pattern.

A formula returns an error or spills incorrectly: Check that the cell reference and quotation marks are correct, and that the spill area is empty. If Excel does not recognize TEXTSPLIT, TEXTBEFORE, or TEXTAFTER, use Text to Columns or an alternative supported by your version. If results contain unwanted spaces, consider trimming the pieces with TRIM where appropriate.

Frequently Asked Questions

Can I split a cell into multiple columns in Excel?
Yes. Use Data > Text to Columns for a one-time split, or use TEXTSPLIT in a compatible version for formula results.

How do I split text at a comma?
In Text to Columns, select Delimited and check Comma. With a supported formula, enter =TEXTSPLIT(A2,",").

How do I split a full name into first and last names?
If every name contains exactly one space, use TEXTBEFORE and TEXTAFTER with a space delimiter, or use Text to Columns with Space selected. Names with middle names or multiple spaces need extra handling.

Can I split text into rows instead of columns?
TEXTSPLIT can return results down rows by specifying a row delimiter, for example =TEXTSPLIT(A2,,",") for comma-separated text. The destination cells below must be empty.

Why does Excel remove zeros at the start of a split value?
Excel may recognize the result as a number. Set the destination column to Text before splitting, or choose Text as the column data format in the Text to Columns wizard.

Will formula results update when I edit the original text?
Yes, formula-based results recalculate when the referenced cell changes. Text to Columns creates a one-time result and does not stay linked to the source.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.