Excel Split Cell Into Columns: Methods and Tips

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns and follow the wizard to split the contents across neighboring columns. For repeatable or formula-driven splits, use TEXTSPLIT instead; either way, check that the cells to the right are empty so you do not overwrite data.

Use Text to Columns for a quick split

Text to Columns is Excel’s built-in tool for dividing existing entries in one column. It works well when each entry follows a consistent pattern, such as a comma-separated list, a tab-separated export, or a code with a fixed character layout. The original values are replaced by the first part of the split unless you choose another destination.

  1. Select the cells you want to split. Selecting just the data range helps avoid applying the operation to unrelated rows.
  2. Choose Data > Text to Columns.
  3. In the wizard, choose Delimited if a character such as a comma or space marks each boundary, or Fixed width if the parts occupy consistent character positions.
  4. Set the delimiter or break positions, check the preview, and choose Next as needed.
  5. Choose a destination if you want to preserve the source column, then select Finish.

For example, a column containing entries such as Rina Patel, Sales can be split at the comma into a name and department. The result goes into adjacent columns on the same row. Excel does not make the original cell physically narrower or create separate pieces inside it; it writes the resulting values into grid cells.

Choose the right delimiter and options

A delimiter is the character Excel uses to decide where one piece ends and the next begins. The correct choice depends on the data, not simply on what appears most often in the cell. In the wizard’s Delimited step, you can choose common separators or enter a character in Other. The preview is useful for spotting an incorrect choice before Excel writes the results.

  • Comma: Useful for simple comma-separated values, such as North,West.
  • Space: Can separate first and last names, but may create extra columns for middle names or multiword surnames.
  • Tab: Often appropriate for text copied from another table or exported from a system.
  • Semicolon or another character: Useful when the source uses that mark instead of a comma.
  • Fixed width: Appropriate when character positions mark the same boundaries in every row, such as a consistently formatted code.

Some exports put text inside quotation marks, especially when a field itself contains a comma. The wizard’s text qualifier setting helps Excel treat the enclosed text as one field instead of splitting at every comma. If a row contains Patel, Rina, Sales, for instance, a plain comma split cannot know whether the first comma is part of a name or a separator. Reliable quoting in the source file, or a different delimiter, is needed to remove that ambiguity.

Consecutive delimiters can represent an empty field. In a row such as Rina,,Sales, the blank space between the two commas may mean that no middle name was provided. Check the wizard’s option for treating consecutive delimiters as one, and use it only if empty fields should be discarded. If the blank position carries meaning, keep consecutive delimiters separate.

Split with a formula using TEXTSPLIT

Use a formula when the source should remain unchanged, when values may be updated later, or when you want a split that recalculates automatically. TEXTSPLIT is available in Microsoft 365 and Excel 2024. It returns a dynamic array, so the results spill into neighboring cells without copying a formula across each output column.

With a comma-separated entry in A2, such as Rina,Patel,Sales, enter =TEXTSPLIT(A2,",") in B2. The result is Rina in B2, Patel in C2, and Sales in D2. Keep those spill cells empty; otherwise Excel may show a #SPILL! error because it cannot place all the results.

With a full name in A2, such as Rina Patel, enter =TEXTSPLIT(A2," ") in B2 to split at spaces. The output is Rina and Patel. This is convenient for simple names, but it is not a reliable way to separate first and last names when entries may include middle names, compound surnames, or inconsistent spacing.

TEXTSPLIT can also split into rows rather than columns by supplying a row delimiter as its third argument. With values in A2 separated by semicolons, enter =TEXTSPLIT(A2,,";") in B2. The parts spill downward in B2, B3, and the cells below. This is useful when the desired layout is vertical, rather than one result per column.

When a formula returns more than one result, the formula itself lives in the top-left cell of the output area. You can edit the source and the results recalculate, but you cannot edit an individual spilled result directly. To keep a fixed copy, copy the results and use Paste Special > Values. For older Excel versions that do not include TEXTSPLIT, use Text to Columns for a one-time operation or build a formula with functions such as LEFT, MID, FIND, and LEN for a known pattern.

Use fixed width when separators are absent

Not every structured entry has a visible separator. A fixed-width record might place a region code in characters 1–3, an account number in characters 4–10, and a status after that. In this case, choose Fixed width in the Text to Columns wizard. Excel displays a preview where you can add, move, or remove break lines to set the divisions.

Fixed width is best only when the character positions stay consistent in every selected row. It can split the wrong information if entries have different lengths, extra spaces, or optional sections. Review several representative records in the preview, including the shortest and longest entries, before finishing. If the source format changes regularly, a delimiter-based split or a Power Query workflow may be easier to maintain.

Preserve the source and protect important values

Before using Text to Columns, make sure the destination area has enough empty columns. If you split a selected cell into three pieces, Excel needs room for the results in that row. Existing content to the right can be overwritten, so insert blank columns or choose a separate destination when necessary. Keeping a copy of the original column is also sensible when the source is difficult to recreate.

Be especially careful with dates, long numbers, and codes that begin with zero. Excel may interpret split results as numbers or dates, changing how they are stored or displayed. A code such as 00427 may become 427 if interpreted as a number. In the wizard, review the column data format options and set identifier columns to Text when leading zeros or exact characters matter. For dates, choose the appropriate date order if the source format is unambiguous, and inspect the result afterward.

Text to Columns can be used to change how existing data is interpreted, not just to separate it. For example, choosing a date format incorrectly can turn a day-month value into a different date than intended. If the data is valuable, work on a duplicate sheet or save a copy of the workbook first. Undo may reverse a recent operation, but keeping the source intact is a safer way to verify a large transformation.

When Power Query is a better fit

For a one-off list, Text to Columns is usually quicker. Power Query is more appropriate when you regularly import files, need to repeat several cleanup steps, or want a refreshable process. In Excel for Microsoft 365 on Windows, start from Data > From Text/CSV for a text or CSV file, or load an existing table through the Data tab’s data-import options. In the Power Query Editor, select the relevant column and use Home > Split Column, then choose a delimiter or another split rule.

Power Query lets you preview transformations and apply them again when refreshing the query. That can save time with recurring monthly exports, but it takes more setup than splitting a short list directly on a worksheet. The result is loaded back to a worksheet or data model rather than behaving like a formula that recalculates in place every time a source cell changes. Excel for Mac and Excel for the web may present different import options and ribbon layouts; if a described command is not available, use the desktop Windows workflow or a worksheet formula supported by your version.

Common problems and how to fix them

  • All text stays in one column: Reopen the wizard and confirm that the selected delimiter matches the source. A tab-separated file will not split at commas unless commas are present.
  • Names or phrases split into too many pieces: A space delimiter treats every space as a boundary. Consider splitting on a more specific character, or use a different approach for data with variable name formats.
  • Results replace other values: Undo immediately if possible, or restore from a saved copy. Before repeating the split, clear or insert enough destination columns, or specify a different destination.
  • Leading zeros disappear: Set the affected output column to Text in the wizard, or format the destination as Text before entering or importing the data. Formatting after Excel has removed zeros does not restore them automatically.
  • A formula shows #SPILL!: Clear cells in the spill range, remove merged cells blocking the output, or move the formula to a larger empty area.
  • Results look like unexpected dates or numbers: Check the data format selected in the wizard and the regional interpretation of the source. Re-run the split with a text format if the values are identifiers rather than quantities.

A related misconception is that Excel’s Merge & Center command can be reversed to divide text into columns. Unmerging cells restores the grid, but it does not separate the text; the content remains in one cell. Use Text to Columns, a formula, or Power Query to divide the contents themselves.

Which method should you choose?

Choose based on how often the data changes and how predictable its structure is. Text to Columns is direct and easy to inspect for a one-time split. TEXTSPLIT is useful for live worksheet formulas in supported versions. Power Query is a good fit for repeatable imports and multi-step cleanup. Fixed-width splitting is reserved for records whose boundaries are defined by position rather than a character.

  • One-time split, consistent delimiter: Text to Columns.
  • Formula that updates with its source: TEXTSPLIT in Microsoft 365 or Excel 2024.
  • Recurring file cleanup: Power Query.
  • Consistent character positions: Text to Columns with Fixed width.

Before choosing, ask what the delimiter means in the data. A comma may separate fields in one file but appear inside an address or description in another. When rows are not consistently structured, no split command can infer the intended meaning perfectly; cleaning or standardizing the source may be necessary first.

Frequently Asked Questions

Can I split one Excel cell into two columns?
Yes. Select the cell or column, choose Data > Text to Columns, select the delimiter, and finish the wizard. Excel places the two parts in adjacent cells on the same row, so leave the destination cells clear.

How do I split text by a space in Excel?
Use Text to Columns and select Space as the delimiter, or use TEXTSPLIT in Microsoft 365 or Excel 2024. A space split can produce more than two pieces when a value contains middle names or multiple words.

Can I split a cell without changing the original?
Yes. In Text to Columns, set a different destination before finishing, or use TEXTSPLIT in an empty cell so the formula results appear elsewhere. For a permanent independent copy of formula results, copy them and paste as values.

Why does Excel not split my comma-separated text correctly?
The data may use a different separator, include quoted commas inside fields, or contain inconsistent rows. Check the wizard preview and text qualifier setting. If commas can occur as part of a field, use a reliably quoted source or a delimiter that does not appear in the field values.

Does splitting a cell work in Excel for Mac or the web?
Excel for Mac includes comparable worksheet tools, but ribbon layout and available import features can differ from Windows. Excel for the web also has a different interface and may not expose every desktop workflow. If Text to Columns is not available in your version, use a supported formula or open the workbook in desktop Excel.

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.