Split Cell Data Into Columns in Excel: Best Methods

To split cell data into multiple columns in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, and follow the wizard to choose a delimiter or fixed-width breaks. For a split that updates when the source changes, use TEXTSPLIT in a separate column instead.

What splitting a cell does

Splitting separates pieces of text stored together in one cell and places them in neighboring columns. For example, the value Jordan Lee,West,Active could become three cells containing a name, region, and status. It is useful when imported or copied data arrives as one long field, but it does not automatically understand what each piece means: you must identify the character or position that marks each boundary.

Splitting is different from wrapping text. Wrap Text changes how content looks inside a cell; it does not move any characters into other cells. Nor is splitting the same as inserting columns, which creates empty space but leaves the original text intact.

Use Text to Columns for a one-time split

Text to Columns is the most direct choice when you want to transform an existing column into separate values. It writes results into the worksheet, rather than leaving a formula that recalculates. Before you begin, check the destination area: output can overwrite cells to the right of the selected column.

Select the cells containing the combined values. A header can be excluded, or included if you want Excel to process it too.
Choose Data > Text to Columns on the ribbon.
Choose Delimited if a character such as a comma or tab separates the values. Choose Fixed width if the boundaries occur at consistent character positions. Select Next.
For Delimited, check the relevant delimiter, such as Comma, Tab, or Space. Use Other to enter a different character. The preview shows how Excel plans to divide the text.
Select Next, review the column data formats if needed, and set a destination if you do not want the results placed over or beside the source.
Select Finish. Inspect the results, especially the first and last rows, before deleting or replacing the original data.

The wizard is a good fit for a one-time cleanup of a pasted list. It does not keep the new columns linked to future edits in the original cells. If you run it again after new records arrive, you will need to repeat the operation for those records.

Choose the right delimiter

A delimiter is the character that marks where one piece ends and another begins. Common examples include commas in a simple comma-separated list, tabs copied from another application, semicolons in some exports, and spaces between words. Select only the delimiter that actually separates fields. Choosing Space for a full name, for instance, splits both the first and last name, which may be unwanted.

Suppose A2 contains Jordan Lee|West|Active. In Text to Columns, choose Delimited, select Other, and enter the vertical bar (|). The output is Jordan Lee in one column, West in the next, and Active in the third. The bar is preserved as a boundary marker, not included in the resulting values.

Multiple delimiters may be appropriate when the source uses more than one separator. For example, a list separated by either commas or semicolons can be handled by selecting both delimiter options in the wizard. Take care with spaces: using both comma and space can split a phrase into more columns than intended. Check the preview before finishing.

Split with a formula that updates

For data that changes or grows, a formula can return split values without replacing the source. In Microsoft 365 or Excel 2024, TEXTSPLIT separates text by a specified delimiter and spills the results into adjacent cells. With the combined value in A2, enter this in B2:

=TEXTSPLIT(A2,",")

If A2 contains Jordan Lee,West,Active, the formula returns Jordan Lee, West, and Active across the row, starting in B2. Make sure the cells to the right are empty; otherwise Excel displays a #SPILL! error because the result cannot occupy its intended range. When the source in A2 changes, the formula recalculates.

To split a full name at a space, with the name in A2, enter =TEXTSPLIT(A2," ") in B2. This divides at every space, so a middle name or a compound name may create more than two outputs. A simple split cannot reliably determine which words belong to a first name or surname in every naming system.

By default, consecutive delimiters can produce empty results. With the text in A2 and spaces between words, use =TEXTSPLIT(A2," ",,TRUE) to ignore empty pieces created by repeated spaces. The omitted third argument leaves the row delimiter unspecified; TRUE is the fourth argument, which tells the function to ignore empty values.

TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in every older Excel release. If Excel does not recognize the function, use Text to Columns or Power Query, or use older text functions where appropriate. A formula-generated result is not itself a set of editable source values; to make a fixed copy, copy the results and use Paste Special > Values.

Use fixed width when there is no delimiter

Some records have fields that occupy consistent character positions rather than using a separator. For instance, a code might always contain a four-character department prefix followed by a six-character identifier. In that case, select Fixed width in the Text to Columns wizard. Excel displays a preview and lets you add, move, or remove break lines to mark where columns should start.

Fixed-width splitting works best when every row follows the same layout. It is fragile when values vary in length: a short department name or an extra character can shift the meaning of everything after it. Review several representative rows in the preview, including the shortest and longest values. If the format is not truly consistent, use a delimiter or a more specific transformation instead.

Pick a method for the way you work

Text to Columns: Best for a quick, one-time conversion of an existing range. It is easy to preview, but does not automatically refresh from changed source data.
TEXTSPLIT: Best when you want formula-driven results that update with the source. It requires a compatible Excel version and clear space for the spilled output.
Power Query: Useful for repeatable imports or larger cleanup tasks. Select the source data, choose Data > From Table/Range, then in the Power Query Editor select the column and use Transform > Split Column > By Delimiter. Choose the delimiter and split option, then select Home > Close & Load to return the results to Excel. Refresh the query when the source changes.
Flash Fill: Helpful for a one-off pattern such as extracting first names from examples you type in a neighboring column. Enter a few desired results, then choose Data > Flash Fill. It infers a pattern rather than applying a clearly specified delimiter, so check the results carefully; it may not handle exceptions consistently.

Power Query takes more setup than the wizard, but it can make a recurring import easier to repeat. Flash Fill is convenient when the transformation is based on a pattern rather than a simple separator, but it produces values rather than a live formula relationship. Choose based on whether the task is one-time, recurring, or expected to change.

Common problems and how to prevent them

Existing values get overwritten: Text to Columns places output in adjacent cells by default. Insert empty columns or set a safe destination in the wizard before finishing.
Too many columns appear: A delimiter such as a space may occur inside a field. Pick a more specific separator, or use a different method for fields that need interpretation.
Some rows split differently: Inconsistent source formatting, missing delimiters, or extra delimiters can create uneven results. Check the preview and inspect outlier rows before applying a large conversion.
Leading zeros disappear: Excel may interpret a result as a number, turning a value such as 00421 into 421. In the wizard, select the affected output column in the final step and choose Text as its column data format. Identifiers and postal codes are often better stored as text.
A formula shows #SPILL!: Clear cells in the output area, including cells that look empty but contain spaces or formulas. The formula needs room for every returned piece.
Nothing splits: Confirm that the delimiter you selected is actually present. A visually similar character, such as a hyphen instead of an en dash, will not match the selected delimiter.

A safe habit is to keep an untouched copy of the original column until the output has been checked. This is particularly important when the data contains identifiers, dates, or other values that Excel may automatically interpret and reformat.

Frequently Asked Questions

Can I split one cell into columns without changing the original?
Yes. Use a TEXTSPLIT formula in an empty area of the sheet, with the source cell as its text argument. The formula leaves the original cell alone and updates when that source changes, provided the output cells are clear.

How do I split a full name into first and last name?
For a simple two-part name in A2, Text to Columns with Space as the delimiter or =TEXTSPLIT(A2," ") can separate the words. Names with middle names, multiple surnames, or internal spaces need extra rules; a basic space split cannot identify the correct first/last boundary universally.

Can I split text into rows instead of columns?
Yes, but that is a different layout. In the Text to Columns wizard, splitting on a delimiter places results across columns. TEXTSPLIT can use a row delimiter to return results vertically, or Power Query can reshape data when you need a repeatable transformation.

Why does Text to Columns change numbers or dates?
Excel may recognize the split pieces as numbers or dates and apply a data format. In the wizard’s final step, select a column and set its format to Text when the exact characters matter, such as for account codes or values with leading zeros.

Does splitting update automatically when I edit the source?
Text to Columns creates values and does not maintain a link to the original. A TEXTSPLIT formula updates when its source changes; a Power Query result generally needs to be refreshed after its source data changes.

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.