Convert Text to Columns Wizard in Excel: How It Works

How-to basics · Updated November 3, 2026

In Excel for Microsoft 365 on Windows, select the cells containing the text, then choose Data > Text to Columns to open the wizard. Choose Delimited to split at characters such as commas or tabs, or Fixed width to split at positions you set; review the preview and choose column formats before selecting Finish.

What the Text to Columns wizard does

Text to Columns takes values stored together in one column and distributes their parts across neighboring columns. For example, a cell containing Jordan Lee,Sales,Portland can become three cells: a name, a department, and a city. It is useful when a worksheet contains pasted, downloaded, or exported data in a consistent layout.

The wizard changes worksheet cells; it does not merely change how a value looks. It can also interpret text as numbers or dates, depending on the choices you make. That distinction matters for postal codes, product identifiers, account numbers, and other values whose characters should be preserved exactly.

The tool is designed for dividing data into columns, not for parsing every possible kind of text. If a field itself contains the separator—for instance, a quoted address with a comma—the delimiter and text qualifier settings determine whether Excel keeps that field together. Inspecting the preview is therefore part of the job, not an optional formality.

Prepare the worksheet before splitting

Text to Columns can write results into the columns to the right of the selected data. If those cells already contain information, the output can overwrite it. Before starting, check the destination area and make a copy of the source column or work on a duplicate sheet when the original data is important.

  • Make sure the selected range contains the records you intend to split, usually one source column with a row per record.
  • Leave enough blank columns to the right for the widest expected result.
  • Decide whether the first row is a header. The wizard does not automatically treat the first row as a header; select data and set the destination so the header is not unintentionally split or displaced.
  • Check whether values contain leading zeros, long digit strings, or date-like text. These may need the Text format to avoid conversion.
  • Look for inconsistent separators, extra spaces, or blank records that could produce unexpected columns.

If the source is an Excel table, splitting can interact with the table’s existing columns and structure. For a predictable result, consider converting a copy to a normal range or placing the output in a separate blank area. You can then review the split before replacing or reorganizing the original data.

How to use the wizard in Excel for Windows

  1. Select the cells in the column you want to split. Avoid selecting unrelated columns unless you intend to process them too.
  2. Choose Data > Text to Columns. The Convert Text to Columns Wizard opens.
  3. On the first screen, choose Delimited if a character separates the fields, or Fixed width if each field occupies a consistent position. Select Next.
  4. Set the delimiter or column breaks on the next screen and inspect the data preview. Adjust the settings until each field appears in the intended column, then select Next.
  5. On the final screen, select each preview column if needed and choose its data format. Set a destination if you do not want results to start in the selected range, then select Finish.

Excel may display a warning if the destination cells contain data. Stop and check the destination rather than accepting an overwrite you did not intend. After finishing, inspect several rows—not just the first one—to confirm that the same split worked throughout the range.

Delimited and fixed-width data

Delimited is the right choice when a consistent character marks the boundary between fields. The wizard offers common delimiters such as Tab, Semicolon, Comma, and Space, as well as an option to enter another character. If the source uses a vertical bar, for example, choose Other and enter |. The preview updates as you change selections.

The Treat consecutive delimiters as one option is useful when repeated separators mean “one boundary,” such as several spaces between words. Leave it off when repeated delimiters represent empty fields. For example, in 17,,29, two commas may indicate that the middle field is blank. Treating consecutive delimiters as one can remove that empty position and shift the last value into the wrong column.

A text qualifier tells Excel which quotation mark, if any, encloses a field. In a comma-separated record such as 42,"Portland, Oregon",active, a quotation mark qualifier allows the comma inside the quoted place name to remain part of that field instead of creating an extra column. This works only when the source follows a consistent quoting convention; malformed or irregular records still need review.

Choose Fixed width when boundaries are based on character positions rather than separators. In its preview, click to add a break line, drag a line to move it, or double-click a line to remove it. A fixed-width report might put an employee code in characters 1–8, a name in characters 10–29, and a status after that. This method works best when every row follows the same layout. If names have varying lengths and are separated by spaces, Delimited is usually more suitable.

Choose column formats carefully

On the final wizard screen, Excel lets you choose a format for each output column. General is convenient for ordinary numbers and dates, but it allows Excel to interpret the content. A value such as 00127 may become 127; a string resembling a date may become a date value; and a long numeric identifier may lose digits because Excel stores only 15 significant digits of numeric precision.

Choose Text for values that must remain character-for-character, including postal codes with leading zeros, codes with letters and digits, and long identifiers. A text value will not behave like a number in arithmetic until it is converted, so use this format only where preserving the original characters is more important than doing numeric calculations.

The wizard also offers date formats, including choices that specify the order of year, month, and day. Use the format that matches the source. For instance, 03/04/2025 can mean March 4 or April 3 depending on the date convention; selecting a matching date order helps Excel interpret it correctly. Excel stores real dates as serial values and displays them using a date format, so the visible appearance may differ from the original text even when the date has been interpreted as intended.

The Advanced option on the final screen lets you specify decimal and thousands separators. This is useful when a file uses conventions different from your Excel regional settings, such as a period for thousands and a comma for decimals. Check the preview after changing these settings; otherwise a number can be interpreted with the wrong scale or as text.

Common problems and how to avoid them

  • Everything lands in one column: The chosen delimiter may not match the source, or the separator may be part of a quoted field. Try the correct delimiter and qualifier, then check the preview.
  • Values shift into the wrong fields: Repeated separators, missing fields, or irregular rows can change the number of columns. Check whether consecutive delimiters should count as one, and examine rows with blanks separately.
  • Leading zeros disappear: Excel likely interpreted the values as numbers. Undo immediately if possible, rerun the wizard, and set the affected output column to Text.
  • A date is reversed or changed: The source’s date order may not match Excel’s interpretation. Select an appropriate date order in the wizard or import the field as Text when it should not become a date.
  • Existing data is replaced: The output destination overlapped populated cells. Use Undo (Ctrl+Z on Windows; Command+Z on Mac) if the action is still undoable, then rerun with a blank destination.
  • Some rows split differently: The source may not be consistently structured. A single wizard setup applies to the selected data, so exceptions may need cleanup or a more controlled import process.

Undo is helpful, but it is not a substitute for keeping a source copy, especially when several later edits have been made. If the result is wrong, stop and evaluate the workbook before continuing; repeated edits can make it harder to restore the original layout.

When to use another method

Text to Columns is a good fit for a one-time split of a reasonably consistent column. It is also straightforward when a person needs to inspect the rules and adjust them interactively. It is less convenient when new source rows arrive regularly, when the source structure changes, or when the same transformation must be repeated across several files.

For recurring cleanup and imports, Power Query can provide a refreshable process. In Excel for Microsoft 365 on Windows, the Get & Transform Data tools on the Data tab can import data and split a column by a delimiter or by positions. The exact available choices depend on the data source and the Power Query interface. After setting up the steps, refresh the query when the source changes instead of repeating each manual operation.

For dynamic worksheet results, Microsoft 365 and Excel 2024 include the TEXTSPLIT function, which can split text into rows or columns based on delimiters. It is useful when the source cell should remain untouched and the result should update when that cell changes. Unlike the wizard, a formula-based result depends on the formula and its settings, and spilled results need clear cells around them. Excel 2021 and earlier do not include TEXTSPLIT.

Excel for Mac has a Text to Columns command on the Data tab, though the dialog’s appearance and some controls can vary by release. Excel for the web may have a different set of data tools than desktop Excel; if the command or a required setting is not available in your browser, open the workbook in the desktop app. Do not assume a web or Mac dialog has exactly the same layout as the Windows wizard.

Frequently Asked Questions

Where is Text to Columns in Excel?
In Excel for Microsoft 365 on Windows, select the source cells and choose Data > Text to Columns. The wizard appears after you select the command.

Can Text to Columns split a cell at a space?
Yes. Choose Delimited and select Space. If multiple spaces should count as a single boundary, select Treat consecutive delimiters as one. If a space can occur inside a field that must stay together, check the preview and consider a different delimiter or a text qualifier.

Does Text to Columns delete the original text?
It writes the split results beginning at the selected range or the destination you specify. If that destination overlaps the source, the first output column replaces the source values. Choose a blank destination when you need to preserve the original column.

How do I keep leading zeros when splitting?
On the final wizard screen, select the relevant output column and choose Text before you finish. If Excel has already removed the zeros, undo the operation and repeat it with Text selected; formatting the shortened number afterward cannot reliably restore the original zeros.

Can I split one column into more than two columns?
Yes. A delimited field can produce several output columns when its separator occurs multiple times, and fixed-width data can have multiple break lines. Make sure the destination has enough empty columns and verify that each row has the expected structure.

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.