How Text to Columns Works in Excel

Excel’s Text to Columns tool splits the contents of one column into separate columns, using either characters such as commas and tabs as separators or positions you specify. In Excel for Microsoft 365 on Windows, select the cells, go to Data > Text to Columns, choose Delimited or Fixed width, review the preview, and finish the wizard.

What Text to Columns does

Text to Columns takes text stored in a cell and distributes parts of it across neighboring columns. For example, a cell containing Jordan Lee,Seattle,WA can become three cells: Jordan Lee, Seattle, and WA. The tool changes the worksheet data; it does not create a live link to the original text or keep the pieces synchronized if you later edit the source.

It is especially useful when data arrives as a single field from a downloaded file, copied report, or older system. Common jobs include separating names, splitting a list of values, and dividing codes at a consistent character. It can also help clean up text that appears in the wrong column after a paste or import.

The wizard works on a selected range in a worksheet. It is not the same as opening a delimited file: an import process may offer additional controls, while Text to Columns acts on data that is already in the sheet. Because the results occupy columns to the right of the source, check that those cells are available before you run it.

Choose Delimited or Fixed width

The first decision in the wizard is how Excel should identify the split points. Choose Delimited when a particular character or mark separates the pieces. Choose Fixed width when each piece occupies a predictable position in the text, whether or not a separator is present.

Delimited: Suitable for entries such as Jordan,Lee,Seattle, where commas mark boundaries, or Jordan Lee, where a space separates words.
Fixed width: Suitable for lines such as AB10420261102 if the first five characters are always an ID and the remaining characters follow a consistent layout.

Fixed width is reliable only when the character positions mean the same thing on every row. If names have different lengths or records have optional sections, a fixed break can cut through a value. Delimited mode is usually easier to maintain when the source uses a consistent separator.

How the Delimited option works

After selecting Delimited, choose the character that marks each boundary. The wizard offers common delimiters such as Tab, Semicolon, Comma, and Space, as well as an option to specify another character. The preview updates as you change selections, so use it to confirm that each piece lands in the intended column.

For example, if the source is Order 47|Blue mug|2, choose Other and enter the vertical bar. A comma is not a safe delimiter for a field like Seattle, WA unless the data format protects that comma. Text qualifiers can do that: in a delimited format, quotation marks can enclose a field so punctuation inside the quoted text is treated as part of the value rather than a split point. Check the preview with representative rows, especially if some fields contain punctuation.

The Treat consecutive delimiters as one setting can be helpful when repeated separators are meant to represent a single boundary. For example, it can prevent several spaces in a row from creating empty columns. Leave it off when empty fields carry meaning. In a sequence such as North,,Active, the two commas may indicate that the middle field is intentionally blank; treating consecutive delimiters as one would change that structure.

How Fixed width works

In Fixed width mode, Excel displays a preview with vertical break lines. Click in the preview to add a break, drag a break to reposition it, or double-click a break to remove it. Each break marks where one output column ends and the next begins. Review several rows, not just the first, to ensure that the same positions separate the same kinds of information throughout the selected range.

Imagine a report in which every row begins with a six-character product code, followed by a two-character region code, followed by a quantity aligned in a fixed position. Breaks at those positions may work well. If the report has wrapped lines, variable-length codes, or spacing that changes from row to row, the preview may reveal that Fixed width is not a good fit. In that case, use a delimiter if one exists, or clean and reshape the source with another method.

Set the output and data formats

Text to Columns can place the result in the original column or at a destination you specify. If the source is in column A and you leave the default destination, Excel writes the separated pieces starting in column A and into columns to its right. Existing values in that area can be replaced, so the safer choice is often a blank destination range or a copy of the original data on another sheet. Excel may warn about cells that would be overwritten; do not dismiss the warning without checking.

The wizard also lets you set a format for each output column. General asks Excel to interpret values where possible. Text preserves entries as text, which matters for IDs, postal codes, and account numbers with leading zeros. A date format lets you identify the order of date components, such as MDY or DMY. You can also choose not to import a particular output column when it is not needed.

For instance, if a code is 004218, General may convert it to the number 4218 and discard the leading zeros. Setting that output column to Text preserves 004218. This distinction matters even when the value contains only digits: a product code is an identifier, not necessarily a number intended for arithmetic.

Dates, numbers, and regional settings

Excel may interpret separated text as numbers or dates. A value such as 03/04/2026 is ambiguous: it can mean March 4 or April 3, depending on the date convention. In the wizard, select the relevant output column and choose Date with the matching order. If the value is an identifier or must remain exactly as written, choose Text instead.

The wizard’s Advanced settings let you specify decimal and thousands separators. These are useful when the source uses conventions different from the computer’s regional settings. For example, a source may write one thousand and a half as 1.000,5, while another convention writes 1,000.5. If Excel interprets the separators incorrectly, the resulting number may be wrong or stored as text. Confirm the settings against the source format and inspect sample results before applying them to a large range.

When exact display matters, remember that Excel stores dates and numbers as values and applies formatting to how they appear. Text to Columns can convert text into a date or number, but the displayed result may then follow the cell’s number format. For a clean import, verify both the underlying interpretation and the visible output.

A safe, practical workflow

Keep a copy of the source data, or work in a duplicate sheet. This makes it easier to compare results or start over.
Select the cells containing the text to split. Avoid selecting a whole column if unrelated data below the records could be affected.
Choose Data > Text to Columns, then select Delimited or Fixed width.
Set delimiters or break positions, and check the preview against several representative rows.
Choose the output destination and set formats for fields that need to remain text or be recognized as dates.
Select Finish, then check the resulting columns for missing values, misplaced pieces, changed leading zeros, and unexpected date or number conversions.

If the result is wrong, use Undo immediately or restore the source copy and repeat the wizard with corrected settings. After other workbook changes, it may be less convenient to undo, which is another reason to preserve the original data. A quick check of the first, last, and most unusual records can catch errors that a preview of only the first row would miss.

Common problems and how to avoid them

Values split at the wrong place: The selected delimiter may also appear inside a field, or a space may separate words within a name. Use a more specific delimiter, a suitable text qualifier, or a different approach. Always test rows containing punctuation or extra spaces.

Leading zeros disappear: Excel has treated an identifier as a number. Set that output column to Text before finishing, or restore the original data and rerun the split. Applying a number format afterward may change how a value looks, but it does not necessarily restore zeros that were removed during conversion.

Dates change meaning: The source and Excel may use different month-day orders. Select the correct date order in the wizard or import the field as Text if it should not be interpreted as a date.

Existing data is replaced: The split output needs space to the right of the source. Choose an empty destination before finishing, and confirm any overwrite warning only if replacing those cells is intentional.

Blank fields disappear: Consecutive delimiters may represent an empty field. Turn off the setting that treats consecutive delimiters as one when preserving empty columns is important.

Text to Columns compared with alternatives

Text to Columns is a good choice for a one-time or occasional split that follows a stable pattern. Unlike a formula, it writes values directly into cells and does not recalculate when the source changes. That makes it straightforward for a finished cleanup, but less suitable when new records will be added regularly and the split must update automatically.

For a repeatable process, Power Query can import and transform data, then refresh the transformation when the source changes. It is useful for recurring files and more involved cleanup, though it requires setting up a query. In Microsoft 365, functions such as TEXTSPLIT can split text through formulas; that approach can update with its source, but requires a compatible Excel version and a formula-based workflow. If data is already laid out in a table or arrives in a structured format, importing it with the appropriate delimiter may be cleaner than pasting it into a worksheet and splitting it afterward.

Text to Columns is a desktop Excel wizard. In Excel for Microsoft 365 on Windows, use Data > Text to Columns. The Mac version has the equivalent tool in the Data tab, although the dialog and ribbon layout can differ. Excel for the web may not expose the same full wizard in every current interface; if the command is unavailable, open the workbook in desktop Excel or use an available import or formula method.

Frequently Asked Questions

Does Text to Columns delete the original text?
It writes the split results starting at the source location by default, so the original cell’s contents are replaced by the first output piece. Preserve a copy or choose a blank destination if you need the original intact.

Can I split a cell by a character that is not listed?
Yes. Choose Delimited, select Other, and enter the character that separates the fields. Check the preview to make sure that character is not also used inside values that should stay together.

How do I split first and last names?
Choose Space as the delimiter only if each entry has a consistent two-part name. Names can include middle names, multiple surnames, or spaces within a family name, so a simple space split may not produce reliable first-name and last-name columns.

Can Text to Columns keep leading zeros?
Yes. In the wizard, select the output column and set its format to Text before you finish. This is important for codes and identifiers that look numeric but should not be used for arithmetic.

Can I combine the columns again afterward?
Text to Columns does not provide a one-click reverse operation. You can join values with a formula or another data-transformation method, but you need to choose the separator and account for blank fields so the reconstructed text matches the original.

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.