Excel Split Cells Into Multiple Columns

How-to basics · Updated November 21, 2026

To split text from one Excel cell into separate columns, select the cells and choose Data > Text to Columns, then follow the wizard to choose a delimiter such as a comma or space. In Microsoft 365, you can also use the TEXTSPLIT function when you want a formula-based result that can update with the source data.

What splitting a cell means in Excel

Excel cannot divide one grid cell into smaller, independent cells. Instead, splitting a cell means taking its contents—for example, a full name, address, or comma-separated list—and distributing the pieces across neighboring columns. The original value may be replaced by the split results, or you can direct those results to another location.

This distinction matters because the commands for splitting text do not change the worksheet’s grid structure. If a cell contains Jordan Lee, Marketing, Denver, a split operation can put each comma-separated field in a different column. It does not create three smaller cells inside the original cell.

Before splitting, decide whether the pieces should be permanent values or remain connected to the original text. Text to Columns is convenient for a one-time conversion. A formula or Power Query is usually more suitable when the source may change or the process must be repeated.

Use Text to Columns for a one-time split

Text to Columns is Excel’s built-in tool for separating text by a delimiter or by positions in the text. In Microsoft 365 for Windows, the path is Data > Text to Columns. It works well for lists pasted from another system, provided the data has a consistent pattern.

  1. Select the cells containing the text. For a single column, select the data cells rather than the entire worksheet column if you want to keep the operation focused.
  2. Choose Data > Text to Columns. In the wizard, select Delimited if a character separates the fields, or Fixed width if fields start at consistent positions.
  3. For Delimited, choose the relevant delimiter—such as Tab, Semicolon, Comma, or Space—or enter a character under Other. Check the preview to see where Excel will break the text.
  4. Review the final wizard screen. Set a destination if you want to keep the original column, and check the column data formats if a field should be treated as text or as a date.
  5. Select Finish. Inspect the results before deleting or moving the original data.

For example, if A2 contains Jordan Lee,Marketing,Denver and you choose a comma delimiter, Excel places the three fields in adjacent columns on that row. If the source contains a space after each comma, the second and third results may begin with a space. You can account for that by cleaning the source first, choosing a delimiter that includes the space where practical, or using a formula such as TRIM with TEXTSPLIT.

Choose the right delimiter

A delimiter is the character or marker that separates one piece of text from the next. Commas, tabs, semicolons, pipes, and spaces are common examples. The right choice depends on the data—not simply on what looks like a separator in a few sample rows.

  • Comma: Useful for values like SKU-18,Blue,12, unless a field itself can contain commas.
  • Tab: Often appropriate for data copied from a web page or exported as tab-separated text.
  • Space: Can separate simple pairs such as first and last name, but is unreliable for full names, addresses, or phrases with multiple words.
  • Other: Choose this for a consistent character such as a pipe (|) or slash (/).

When a delimiter appears inside a field, a plain split can create too many columns. For instance, splitting an address on every comma may separate the street, city, and a company name that also contains a comma. Text to Columns includes a text qualifier option in its wizard; quoted fields in properly formatted delimited data can use quotation marks to indicate that a delimiter inside the quoted text belongs to the field. Check the preview and test a few representative rows rather than assuming every comma has the same meaning.

The wizard also has an option for treating consecutive delimiters as one. This can help when inconsistent spacing creates repeated separators, but it can also remove meaningful empty fields. If two commas in a row represent a blank field in your source, treating consecutive delimiters as one may shift the following values into the wrong columns.

Split with a formula that updates

In Microsoft 365 and Excel 2024, TEXTSPLIT can divide text into columns with a formula. Unlike a one-time conversion, the result recalculates when the source cell changes. Enter the formula in an empty cell with enough blank columns to its right for the results to spill.

With the text Jordan Lee,Marketing,Denver in A2, enter this formula in B2:

=TEXTSPLIT(A2,",")

The result spills across B2:D2 as Jordan Lee, Marketing, and Denver. The comma inside the quotation marks is the column delimiter. If the source is Jordan Lee, Marketing, Denver, a comma-only split retains spaces at the start of later fields. With the same source in A2, this version trims the split results:

=TRIM(TEXTSPLIT(A2,","))

It returns the same three fields without leading or trailing spaces. If a formula displays a spill error, check that the cells it needs to fill are empty and not merged. TEXTSPLIT is available in Microsoft 365 and Excel 2024; it is not available in some older perpetual Excel releases. On Mac or Excel for the web, availability depends on the Excel version or account, but the formula syntax is the same where the function is supported.

TEXTSPLIT can also divide text into rows by supplying a row delimiter as its third argument. For example, if A2 contains Red;Green;Blue, the formula below in B2 puts the items down a column rather than across a row:

=TEXTSPLIT(A2,,";")

Here the second argument is left empty because no column delimiter is specified, and the semicolon is the row delimiter. The result is Red, Green, and Blue in successive rows. This is useful when a cell holds a list that should become a vertical set of records, but make sure the rows below the formula are available for the spill.

Split text at fixed positions

Not every dataset uses a visible separator. If each value follows a fixed-width pattern—for example, a three-character region code followed by a six-character item code—select Fixed width in the Text to Columns wizard. Excel displays a preview where you can add or move break lines to mark the positions between fields.

Fixed width is appropriate only when the character positions are consistent across the selected rows. It is a poor fit for names or descriptions that vary in length. A break that correctly separates one row may cut through a word in another. Review several of the longest and shortest examples in the preview before applying the split.

For codes that must keep leading zeros, use the wizard’s column data format options and set the relevant output column to Text. If Excel interprets a code such as 00421 as a number, it may display it as 421. Text formatting preserves the characters as entered; it is generally preferable when the value is an identifier rather than a quantity for calculation.

Use Power Query for repeatable imports

Power Query is useful when you regularly receive a file that needs the same cleanup. It can split a column during an import or transformation and then repeat those steps when you refresh the query. This avoids manually running Text to Columns every time a new version of the source arrives.

In Excel for Microsoft 365 on Windows, select a cell in a properly arranged data range and choose Data > From Table/Range. If Excel asks, confirm that the range has headers if it does. In the Power Query Editor, select the column, then choose Transform > Split Column > By Delimiter. Select the delimiter and whether to split at each occurrence or at a specified occurrence, review the preview, and choose Close & Load to return the transformed data to Excel.

Menu details and available connectors can differ by Excel platform and release, but the general approach is to define the transformation once and refresh it when the source changes. Power Query is especially helpful for repeated CSV or workbook imports, and for combining several cleanup operations. It may be more setup than needed for a single small list.

Protect your data and troubleshoot results

Text to Columns can overwrite cells to the right of the selected range. Before choosing Finish, make sure those cells are empty or set a destination in the wizard. If you need the original text for reference, copy the source column first or send the results to a separate area. A backup is also sensible before applying a bulk transformation to important data.

  • Results land in unexpected columns: Recheck the delimiter and preview. A delimiter occurring inside a field can create extra columns.
  • Values lose leading zeros: Set the output column to Text in the wizard, or import the data as text where appropriate.
  • Dates change format: Review the column data format before finishing. Excel may recognize date-like text and convert it to a date value.
  • A formula shows #SPILL!: Clear the cells blocking its output, and check for merged cells in the spill area.
  • Some rows split differently: Look for inconsistent separators, extra spaces, embedded delimiters, or missing fields in the source.

If you have just used Text to Columns and the result is wrong, Ctrl+Z on Windows will usually undo the operation as long as you have not made intervening changes that prevent it. On Mac, use Command+Z. Undo is not a substitute for keeping a copy when the source data is valuable.

Which method should you choose?

Choose Text to Columns for a quick, one-time split of a clean column. It is visible, does not require a formula, and offers both delimiter-based and fixed-width options. Its main limitation is that it produces a conversion rather than a live relationship with the original text.

Choose TEXTSPLIT when you want results driven by a formula and have a supported Excel version. It is convenient for a worksheet that updates as source cells are edited, but it needs free space for the spilled results and may not be suitable when you must send the workbook to people using older Excel versions.

Choose Power Query when you repeat the same import or cleanup process. Choose Flash Fill for a small, irregular set of examples when you can demonstrate the pattern: type the desired result in a neighboring column, then use Data > Flash Fill in Windows and review what Excel produces. Flash Fill is pattern-based, so it may not consistently handle every exception in a large or changing dataset. None of these tools is automatically the best choice; the important questions are whether the separator is reliable, whether the process must update, and whether exceptions need review.

Frequently Asked Questions

Can I split one Excel cell into two columns?
Yes. Use Data > Text to Columns and choose the character that separates the two parts, or use TEXTSPLIT in a supported version. The contents are distributed into separate worksheet cells; Excel does not divide a grid cell internally.

How do I split a full name into first and last name?
If every name contains exactly one space, Text to Columns with Space as the delimiter can work. If names include middle names, compound surnames, or inconsistent spacing, splitting at every space may create extra fields or incorrect results. Inspect the data and decide what counts as a first or last name before applying a bulk split.

Can I split a cell without changing the original?
Yes. In Text to Columns, choose a different destination in the wizard, or copy the source data to another column first. With TEXTSPLIT, put the formula in a separate empty area; the source cell remains unchanged.

Why does Excel say there is not enough room to split?
Text to Columns may need to write results into cells to the right that already contain data. Clear those cells or choose a different destination after preserving anything important. For a TEXTSPLIT formula, clear the cells in its expected spill range and check that none are merged.

Does splitting text delete the original value?
Text to Columns writes the split results into worksheet cells and can replace the original column’s contents, depending on the destination. A formula leaves the original value in place, while Power Query creates transformed output separate from the original source. Keep a copy if you need to retain the unsplit text.

Related

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.