Excel Text to Columns: Split Data Cleanly and Safely

How-to basics · Updated November 21, 2026

To transform text into separate columns in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited or Fixed width, set the options, choose a destination if needed, and select Finish. The wizard splits existing cell contents into neighboring columns; check the preview and make room first so you do not overwrite other data.

What Text to Columns does

Text to Columns takes values stored in one column and distributes their parts across two or more columns. For example, a cell containing Jordan Lee, Vancouver can become a name in one column and a city in the next when the comma is the delimiter. It is useful for cleaning copied lists, separating imported fields, and making data easier to sort, filter, or analyze.

The command changes the selected cells rather than creating a live link to the original text. If the source data changes later, the split results do not automatically update. Make a copy of the original column if you may need it, or use a formula or Power Query for a repeatable transformation.

Text to Columns is a splitting tool, not a general-purpose cleanup tool. It will not reliably infer every intended field from inconsistent data. A delimiter must actually separate the parts, or fixed-width positions must be consistent. If some rows use commas and others use spaces, for instance, you may need to standardize the source before splitting.

Use the Text to Columns wizard

In Excel for Microsoft 365 on Windows, use these steps for a basic split:

  1. Select the cells in the column you want to split. Include the data rows, and avoid selecting unrelated columns.
  2. Choose Data > Text to Columns. Excel opens the Convert Text to Columns Wizard.
  3. Choose Delimited when a character such as a comma, tab, semicolon, or space marks the boundary. Choose Fixed width when the boundaries occur at consistent character positions.
  4. Review the next screen. For Delimited, select the delimiter and check the preview. For Fixed width, add or adjust the break lines in the preview.
  5. Set the data format for each output column if necessary. Choose a destination other than the source column if you want to retain the original values.
  6. Select Finish, then inspect several output rows, including rows with unusual or missing values.

The wizard usually places the first result in the selected column and fills subsequent parts into columns to its right. If those cells already contain information, the split can replace it. Insert empty columns or choose an empty destination range before finishing. Selecting a destination cell does not itself move the original data; it tells Excel where to put the results.

Choose the right delimiter

A delimiter is the character Excel uses to decide where one piece ends and the next begins. Common choices include commas in address-like records, tabs in copied tables, semicolons in some regional exports, and spaces in simple full names. In the Delimited step, select the matching checkbox and use the preview to confirm that the proposed columns make sense.

For example, if A2 contains 4821|Hardware|West, choose Other and enter the vertical bar character as the delimiter. The output should be 4821, Hardware, and West in three columns. The delimiter must match the data exactly: choosing a comma will not split a pipe-separated record.

When a field contains the same character that separates fields, a text qualifier can protect it. A common example is a quoted CSV value such as "Smith, Jordan",Vancouver: the comma inside the quoted name is part of that field, while the comma after the closing quote separates the city. In the wizard, check the Text qualifier setting and confirm the preview treats the quoted text as one field. If the file has complicated quoting or inconsistent structure, importing it through Excel’s text or CSV import options may offer better control than splitting an already-open column.

The wizard also has an option to treat consecutive delimiters as one. Use it when repeated separators mean a single boundary, such as several spaces between words. Leave it off when each delimiter represents a field boundary and empty fields matter. For instance, two consecutive commas in a CSV-like row may indicate a missing value; treating them as one would remove that empty field and shift later values left.

When fixed width is a better fit

Choose Fixed width if each record uses the same character positions for its fields and there is no reliable separator. A classic example is a plain-text report in which an account code occupies characters 1–8, a date occupies the next 10 positions, and an amount begins at a fixed position. The wizard displays break lines that you can add, move, or remove to define where columns start.

Fixed-width splitting is fragile when the source uses variable-length names, proportional spacing, or inconsistent padding. A break position that works on one row may cut through a word or number on another. Check long and short examples in the preview, and prefer a real delimiter when the file provides one. A fixed-width layout that looks aligned on screen is not always fixed-width data; the apparent spaces may simply be formatting.

Protect dates, numbers, and leading zeros

Excel may interpret split fields as numbers or dates. That can be helpful when you want to calculate with them, but it can also change the original representation. A code such as 00427 can become the number 427, because leading zeros are not significant in an ordinary numeric value. A date written as 03/04/2025 may also be interpreted according to the workbook’s regional date settings, making the month and day ambiguous.

To preserve a field exactly as text, select its column in the wizard’s preview and set Column data format to Text before finishing. Do this for identifiers, postal codes, product codes, account numbers, and date strings whose original form matters. If you want a real Excel date or number instead, use the appropriate format and verify the result. Applying a display format after Excel has already discarded leading zeros does not restore them; you would need to reconstruct them from known rules or return to the original text.

Text to Columns can also be used to convert a column of number-like text into numeric values, but inspect the result before relying on it. A conversion may alter formatting, and some values—such as long identifiers—should not be numeric at all. Keep the raw source when auditability or exact reproduction is important.

Text to Columns versus formulas

Use the wizard for a one-time split when the layout is consistent and you want fixed results. A formula is more suitable when the source may change and you want the output to recalculate. In Microsoft 365 or Excel 2024, the TEXTSPLIT function can split a cell using a delimiter and spill the results into adjacent cells. It is not available in every older Excel version.

With the text North|West|Retail in A2, enter this formula in B2:

With the source value in A2 and the formula starting in B2, =TEXTSPLIT(A2,"|") returns North, West, and Retail across B2:D2.

Formula results need empty cells in the spill area; otherwise Excel can show a spill error. Unlike the wizard, the formula remains connected to A2, so changing A2 updates the result. If your version does not include TEXTSPLIT, the wizard is often simpler for a one-off task. Older formulas can split text too, but they are more involved and need to be designed around the data’s exact pattern.

Use Power Query for recurring imports

If you regularly receive a file that needs the same split, Power Query can make the steps repeatable. In Excel for Microsoft 365 on Windows, select a cell in a structured data range and choose Data > From Table/Range. In the Power Query Editor, select the column, then choose Home > Split Column > By Delimiter. Set the delimiter and splitting behavior, review the preview, and use Close & Load to return the transformed data to Excel.

Power Query records the transformation steps, so you can refresh the query when you replace or update its source, subject to how the source is connected. It is useful for repeatable imports and multi-step cleanup, while Text to Columns is faster for a small manual task. Power Query’s interface and available connectors can differ by platform and Excel edition, so check the commands available in your installation.

Common problems and how to fix them

  • Nothing splits: Check that the selected delimiter is present in the cells and that the intended source column is selected. Look closely at the preview; visually similar characters, such as a hyphen and an en dash, are different.
  • Values overwrite existing data: Undo immediately if possible, or restore the workbook from a saved copy. Before running the wizard again, insert blank columns or set an empty destination cell.
  • Fields move into the wrong columns: Review consecutive delimiters, empty fields, and text qualifiers. Do not treat repeated separators as one when they represent missing values.
  • Zeros disappear or dates change: Set affected output columns to Text in the wizard before selecting Finish, then check sample values. Formatting cannot recover characters already removed during conversion.
  • Only some rows split correctly: The source may use inconsistent separators, extra spaces, or quoted delimiters. Standardize the data first, choose a more appropriate delimiter, or use Power Query for a more controlled transformation.

On Excel for Mac, the Text to Columns command is also available from the Data tab, although wizard presentation and ribbon details can vary by release. Excel for the web has a different feature set and interface; if you cannot find the command, open the workbook in the desktop app or use a supported formula such as TEXTSPLIT where available. Always preserve a copy before transforming important source data.

Frequently Asked Questions

Can I split text into columns without losing the original?
Yes. In the wizard, set a destination cell in an empty area before selecting Finish, or copy the source column first. Confirm there is enough empty space to the right for every resulting field.

How do I split a full name into first and last names?
If every name contains exactly one space between the two parts, choose Space as the delimiter. Names with middle names, compound surnames, or inconsistent spacing need more careful rules; a simple split cannot reliably identify a person’s intended first and last name in every case.

Can Text to Columns split on more than one character?
The wizard’s delimiter choices are generally individual characters, and you can select more than one delimiter type. For a multi-character separator or a more specific rule, use a suitable formula or Power Query and check the results.

Why did Excel remove the leading zeros?
Excel interpreted the field as a number. Repeat the split and set that output column’s format to Text in the wizard. If the zeros have already been discarded and the original is gone, recover the source or rebuild the code using a known fixed length.

Does Text to Columns update when the original text changes?
No. It writes split values into cells; it does not maintain a live connection to the source. Use a formula for recalculation or Power Query for a refreshable transformation.

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.