Excel Text to Columns: Split Names, Dates, and Other Data

In Excel for Microsoft 365 on Windows, select the cells in one column, then choose Data > Text to Columns. Select Delimited to split at characters such as commas or tabs, or Fixed width to set the break positions yourself; check the preview, choose an output destination and formats, then click Finish.

What Text to Columns does

Text to Columns takes text stored in a cell and distributes its parts across neighboring columns. It is useful when a list has been imported or pasted as one long field—for example, when each cell contains a name and department separated by a comma. The tool changes the worksheet cells; it does not simply change how a cell looks.

Suppose A2 contains Mira Chen, Sales, West. Splitting on commas places Mira Chen, Sales, and West in separate cells across the same row. Excel does not automatically know that the first two words are a first and last name, or that the third value is a department. The separator you choose determines where the breaks occur.

This is a one-time worksheet operation, rather than a live formula. After the split, the new cells contain the results, and changing the original text later will not update them. Keep an untouched copy of the source column if you may need to repeat the process or compare the result.

Check the source before splitting

Before opening the wizard, inspect a few representative rows. Look for the actual separator, irregular spacing, blank values, and fields that contain the separator as part of their content. A comma-separated list is not necessarily as simple as one comma per row: an address might contain a comma, or a quoted product description might include punctuation that should stay together.

Make sure there are enough empty columns to the right of the source. Text to Columns can replace existing cell contents in its output area.
Consider copying the source column to a spare worksheet or workbook before transforming it, especially if the data is valuable.
Decide how Excel should treat identifiers such as postal codes, account numbers, and product codes. These can look numeric but should often remain text.
Check whether rows use one consistent pattern. If some records have fewer fields or different separators, the split may produce uneven results that need review.

Do not select entire rows just because you want the output to stay on the same row. Select the cells in the single source column you want to split. Choosing a suitable destination in the wizard lets you keep the original values alongside the results.

Delimited and fixed-width data

The wizard offers two ways to identify a boundary. Choose Delimited when a character or set of characters marks the separation. Common delimiters include commas, tabs, semicolons, spaces, and vertical bars. A tab-delimited export, for example, can be split by selecting Tab; a pipe-separated file can be handled by selecting Other and entering the pipe character.

The preview updates as delimiter choices change. If you select more than one delimiter, Excel treats each selected character as a possible boundary. This can help with data that uses both commas and semicolons, but it can also split a field you meant to keep together. The Treat consecutive delimiters as one option is useful when repeated spaces or delimiters are intended to act like one separator. Leave it off when empty fields between repeated delimiters are meaningful.

Choose Fixed width when the data uses consistent character positions rather than a visible separator. For instance, a legacy export might place a three-character region code at the start of every line, followed by a customer number in a fixed position. In the preview, click to add break lines and drag them to adjust the field boundaries. Fixed width is less suitable when values have variable lengths, because a break at the same character position can cut through different fields on different rows.

Run the Text to Columns wizard

Select the source cells in one column and open Data > Text to Columns.
On the first wizard screen, choose Delimited or Fixed width, then select Next.
For Delimited, select the separator or separators and review the preview. For Fixed width, add or move break lines in the preview. Continue with Next.
On the final screen, select each output column in the preview if you need to assign it a format. Set the Destination field if the results should go somewhere other than the source area.
Review the destination and column formats, then select Finish. Inspect several resulting rows, including the first and last records.

The final screen matters because Excel can interpret some text as a number or date. General is suitable for ordinary values Excel can safely recognize, but it is not always appropriate for codes or dates in an unfamiliar format. Select a column in the preview and choose Text to preserve values as text. If a column contains dates, choose Date and the order that matches the source, such as MDY or DMY. A date written as 04/05/2026 can mean April 5 or May 4 depending on its convention, so confirm the intended order rather than assuming.

Preserve dates, zeros, and other values

Leading zeros are a frequent reason to set a column to Text. If a code is 00427, Excel may treat it as the number 427 under General, because numeric values do not retain unnecessary leading zeros. Setting that output column to Text keeps the identifier as written. This is usually preferable to treating an identifier as a quantity, particularly when it is used for matching or reporting.

Dates need similar care. Excel stores recognized dates as serial values and displays them according to the cell format. If the source is day-month-year but Excel reads it as month-day-year, the displayed result may be wrong even though it looks like a plausible date. On the wizard’s final screen, choose Date and the source order. If the entries are codes that merely resemble dates, choose Text instead.

Text to Columns can also affect values that look numeric, such as long reference numbers. Excel has a limit of 15 significant digits for numeric precision, so a long identifier should be kept as text if every digit must remain exact. After splitting, compare sensitive values with the source. Formatting a cell as Text after Excel has already converted or rounded a value does not restore lost digits.

Common problems and how to fix them

Everything stays in one column: Check that the correct delimiter is selected and that the preview shows the expected breaks. If the source uses a tab or pipe rather than a comma, selecting comma will not split it.
A field splits in the wrong place: Review whether the delimiter appears inside the field. For comma-separated text, a text qualifier such as a double quote can keep a quoted comma within one field. Check the wizard’s text qualifier setting and preview.
Existing data disappears: The destination overlapped cells that already held information. Undo immediately with Ctrl+Z on Windows, if the action is still available, or repeat the operation with an empty destination and restore the original data from a copy.
Zeros disappear or dates change: Excel interpreted the output as numeric or date data. Repeat the split from the original values and set the affected column to Text or choose the correct date order before finishing.
Blank output columns appear: Repeated delimiters may represent empty fields, or the Treat consecutive delimiters as one option may not match the source. Adjust that setting and inspect the preview before completing the split.

If the preview looks correct but the worksheet result does not, check the destination and the selected range. A wrong destination can make a correct split appear to have failed, while selecting cells in the wrong source column can apply an inappropriate pattern. For quoted data, inspect the original text for consistent opening and closing qualifiers; malformed quotes can make field boundaries difficult to interpret.

When to use another method

Text to Columns is a good choice for a straightforward, one-time split. For data that must update when its source changes, a formula may be more suitable. In Microsoft 365 or Excel 2024, TEXTSPLIT can return split results in neighboring cells. With the text in A2 as Mira Chen, Sales, West, enter =TEXTSPLIT(A2, ", ") in an empty cell; the result is Mira Chen, Sales, and West in three cells across the row. The source remains unchanged, and the result recalculates when A2 changes. Leave the spill area empty so Excel has room to display the results.

For a recurring import, consider Power Query instead. In Excel for Microsoft 365 on Windows, open Data > Get Data > From File > From Text/CSV to import a text or CSV file, then use Power Query’s split-column tools and load the result. A saved query can be refreshed when the source file changes, which is more repeatable than manually running Text to Columns each time. Power Query is often unnecessary for a quick one-off cleanup, and its import steps are distinct from editing data already pasted into the worksheet.

Use Text to Columns when you want a direct, manual transformation and can verify the result immediately. Use a formula when the split should remain linked to source cells, or a repeatable import process when the same file structure arrives regularly. For uneven data, first standardize the source or use a method that lets you inspect and handle exceptions rather than assuming every row follows one pattern.

Frequently Asked Questions

Can Text to Columns split text by a space?
Yes. Choose Delimited, select Space, and inspect the preview. If some fields contain multiple words, a space delimiter will split those words too, so use another separator or a different method when those phrases need to stay together.

Does Text to Columns split data into rows?
No. It separates fields into columns along the same row. To turn a vertical list into a horizontal one, or vice versa, use Excel’s Transpose paste option instead; that is a different operation.

How do I keep leading zeros?
On the final wizard screen, select the relevant output column in the preview and set its column data format to Text before selecting Finish. If Excel has already removed zeros, go back to the original source and repeat the split with the text format selected.

Can I undo Text to Columns?
Usually, press Ctrl+Z on Windows immediately after the split, as long as the action remains in Excel’s undo history. On Mac, use Command+Z. If you have continued working or saved over the only copy, restoring a backup may be necessary.

Is Text to Columns the same as splitting a CSV file?
Not exactly. Text to Columns can split text that is already in worksheet cells. When opening or importing a CSV file, Excel’s import tools can offer delimiter and data-type choices before the data is loaded, which can help prevent unwanted date or number conversions.

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.