Excel Text to Columns: How to Split Data

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, select the cells you want to split, then choose Data > Text to Columns and follow the wizard. Choose Delimited to split at characters such as commas or spaces, or Fixed width to split at specific positions; check the destination before finishing so you do not overwrite nearby data.

What Text to Columns does

Text to Columns takes text in one column and distributes parts of it across two or more columns. It is useful when a cell contains several values separated by a consistent character or when values occupy predictable character positions. For example, a comma-separated entry such as Mira Chen,Seattle,WA can become a name, city, and state in separate cells.

The command is a conversion tool, not a live link to the source. Once the wizard finishes, Excel puts the results into worksheet cells. If the source text changes later, the split results do not automatically recalculate. You can run the wizard again, or use a formula when you need an automatically updating split.

Text to Columns does not recognize every kind of structure. It follows the delimiter or positions you specify; it does not know whether a comma belongs to an address, a company name, or a list separator. Review the preview and a few completed rows to make sure the chosen rules match the meaning of your data.

Choose between Delimited and Fixed width

The wizard begins by asking how the text should be divided. The right choice depends on how the source was created:

  • Delimited is for a visible separator between fields, such as a comma, tab, semicolon, or pipe character. It is usually the right option for copied lists and exported text.
  • Fixed width is for records in which each field occupies a consistent position, even if there is no separator. It can work for simple reports with aligned character columns.

A comma-separated line such as 1042,Jordan Lee,Open is delimited. A line such as 1042Jordan LeeOpen is not safely split by commas; fixed width might work only if every record has the same field boundaries. If names or descriptions vary in length, fixed-width positions can cut values in the wrong place.

When unsure, start with Delimited and inspect the preview. If there is no consistent separator and the preview cannot show meaningful fields, the source may need a different cleanup method rather than a guessed split.

Steps for using the Text to Columns wizard

  1. Make a copy of the source column or save a backup if the original values must be preserved. Check that the columns to the right of the source are empty, or decide where you want the output to go.
  2. Select the cells containing the text. You can select a single column or a portion of it; avoid selecting unrelated columns that should not be transformed.
  3. On the ribbon, choose Data > Text to Columns. The Convert Text to Columns Wizard opens.
  4. Select Delimited or Fixed width, then choose Next.
  5. Set the delimiter or column breaks. Use the data preview to confirm that each intended field appears in its own preview column.
  6. Choose Next to set formats and the destination, if needed. Then choose Finish and review the resulting cells.

On current desktop versions of Excel for Mac, the command is also available from the Data tab as Text to Columns, although the dialog’s appearance can differ. Excel for the web may not provide the same full desktop wizard; if the command is unavailable, use a supported formula or open the workbook in the desktop app.

Set delimiters and text qualifiers

In Delimited mode, Excel offers common delimiter choices, including Tab, Comma, Space, and Semicolon, plus an option to enter another character. Choose only the separators that actually divide fields. Selecting both comma and space, for instance, can split a full name into separate first- and last-name columns as well as splitting comma-separated fields.

The Treat consecutive delimiters as one option is useful when multiple separators represent a single boundary. Suppose a list uses spaces between fields but some rows have two spaces. With this option checked, the repeated spaces can be treated as one separator. Leave it unchecked if empty fields between consecutive delimiters are meaningful and must be retained.

A text qualifier tells Excel to treat text inside a chosen enclosing character as one field, even if that text contains a delimiter. For example, a quoted field like "Lee, Jordan" can remain together when the comma is also being used to separate columns. Check the preview: if quoted phrases split unexpectedly, the qualifier setting may not match the file’s format.

The preview is especially important with addresses, descriptions, and other text that can contain punctuation. A comma may separate fields in one dataset but be part of a value in another. The wizard applies the same settings to the selected rows, so a rule that works for the first row may still break later rows with different formatting.

Use Fixed width and control column formats

In Fixed width mode, Excel shows a ruler over a sample of the selected data. Click where a new field should begin to add a break line; drag a line to move it, or remove a line to combine sections. Examine several representative rows before finishing. A break that looks correct for one record may fall inside a longer value in another.

The next wizard screen lets you control how each output column is interpreted. The default General format converts values Excel recognizes as numbers or dates. Text keeps a value as text, which is important for postal codes, product codes, and account numbers that may begin with zero. Date lets you specify an order such as MDY for month, day, year. You can also select a preview column and choose Do not import column (skip) if that field is not needed.

These settings can prevent subtle changes. For example, an identifier such as 00481 may become 481 under General because Excel treats it as a number. A date like 03/04/2025 may be interpreted differently depending on the date order selected. If the value is an identifier rather than a quantity, choose Text; if it is a date, confirm the order matches the source.

Choose a safe output destination

By default, Text to Columns places the first result in the selected source column and fills additional results to the right. If those cells contain information, the operation can replace it. Before choosing Finish, use the Destination box to specify another starting cell, such as D2, or copy the original data to a spare area of the worksheet.

The destination is the upper-left cell for the split output, not a place where Excel automatically creates a new worksheet. Allow enough empty columns for the number of resulting fields and enough rows for all selected records. Check for merged cells or existing values in that area, since they can interfere with the output.

Text to Columns changes worksheet content rather than creating a reversible query or a linked transformation. Ctrl+Z on Windows, or Command+Z on Mac, can usually undo a recent operation, but it is safer not to rely on undo as a backup. If preserving the original is important, copy the source first.

Examples and common problems

For a list of names written as Chen, Mira, choose Delimited and select Comma. The output becomes Chen in the first column and Mira in the next. If the source instead reads Mira Chen, a space delimiter separates the two words, but that is not a reliable way to separate full names in a list containing middle names or multiword surnames.

For text copied from a report where each field always occupies the same character positions, Fixed width may be appropriate. For example, if every record starts with a four-character code followed by a description, a break after the code can separate them. Verify that the code length really is fixed in every row before applying the rule.

Common issues usually come from inconsistent source data or automatic interpretation:

  • Everything stays in one column: confirm the selected delimiter matches the actual separator. Data copied from a webpage may use tabs rather than spaces.
  • Values split into too many columns: remove unintended delimiters. A space delimiter can split phrases, and selecting multiple delimiter types can create extra breaks.
  • Leading zeros disappear: set the affected output column to Text before finishing, or restore the source and repeat the conversion.
  • Dates look wrong: choose the correct date order in the wizard or import the field as Text when it should not be converted.
  • Some rows split differently: check for missing delimiters, extra spaces, embedded punctuation, or quoted text that needs a text qualifier.

For a small dataset, correcting inconsistent separators before splitting can save time. For a large recurring import, consider cleaning the source systematically or using Power Query, which can store repeatable transformation steps rather than requiring the same manual wizard choices each time.

Text to Columns compared with formulas and Power Query

Text to Columns is a practical choice for a one-time split when the input is already in the worksheet and the rules are clear. It creates ordinary cell values, so they are easy to edit, sort, or use in other calculations. Its main limitation is that it does not update the output when the original text changes.

For a dynamic split in Microsoft 365 or Excel 2024, the TEXTSPLIT function can return pieces into adjacent cells. With the text in A2 as Mira Chen,Seattle,WA, enter =TEXTSPLIT(A2,",") in an empty cell; the result spills horizontally as Mira Chen, Seattle, and WA. Keep the cells to the right empty so the result has room to spill. TEXTSPLIT is not available in every older Excel version.

Power Query is useful for repeated imports and more involved cleanup, such as splitting a column by a delimiter while preserving a refreshable sequence of transformation steps. It is generally more setup than needed for a quick one-off conversion. Choose based on whether you need a static result, a formula that recalculates, or a repeatable import process.

Frequently Asked Questions

Where is Text to Columns in Excel?
In Excel for Microsoft 365 on Windows, select the data and choose Data > Text to Columns. The command is also on the Data tab in desktop Excel for Mac, though the interface may look different.

Can Text to Columns split a full name?
Yes. A space delimiter can split names into words, and a comma delimiter can split entries formatted as Last, First. However, names have inconsistent structures, so splitting every full name at a space may not reliably identify first and last names.

Does Text to Columns delete the original text?
It writes the first output field in the source column by default and places additional fields to the right, so the original cell contents are replaced by the first part. Copy the source or choose a different destination if you need to retain the original.

How do I split text at a character that is not listed?
In the Delimited step, choose Other and enter the character that separates the fields, such as a pipe (|). Check the preview to make sure it separates the intended values and does not split text where that character is part of a field.

Can I use Text to Columns in Excel for the web?
The full desktop wizard may not be available in Excel for the web. If you cannot find the command, open the workbook in desktop Excel, or use a formula such as TEXTSPLIT where your Excel version supports it.

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.