To change text to columns in Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns. In the wizard, choose Delimited to split at characters such as commas or spaces, or Fixed width to split at positions you set; review the preview and choose a safe destination before selecting Finish.
What Text to Columns does
Text to Columns takes text stored in one column and distributes its parts across neighboring columns. It is useful when a downloaded list, copied report, or older system export puts several values together in each cell. For example, a column containing Chen, Mira can become one column for the surname and another for the given name. A tab-separated row of product code, description, and price can also be separated into three fields.
The command changes the worksheet data; it is not merely a display setting. Excel writes the split values into cells to the right of the source column, and existing content in those cells can be overwritten. It does not automatically create a permanent connection to the original text. If the source changes later, you may need to run the tool again or use a formula or query-based method instead.
Choose the right split method
Delimited is the usual choice when a consistent character marks the boundary between values. Common delimiters include commas, tabs, semicolons, and spaces. Fixed width is better when each field occupies a predictable position, even if there is no visible separator. The wizard’s preview helps you see whether the proposed boundaries match the data before applying them.
- Choose Delimited for values such as
1042,Blue mug,12.50, where commas separate fields. - Choose Fixed width for a report in which codes always occupy characters 1–6, names characters 7–26, and amounts begin at character 27.
- Pause before using a space delimiter if names, street addresses, or descriptions contain spaces. One name such as
Mary Ann Leecould split into three pieces rather than the two fields you intended.
If every row does not follow the same structure, neither method may produce a clean result in one pass. Inconsistent records often need an initial cleanup or a more flexible formula or transformation process.
Step-by-step: use the Text to Columns wizard
- Make a backup or copy the source column. This gives you a way to restore the original text if the split is not what you expected.
- Select the cells to split. Usually, select the data cells in one column rather than the entire worksheet. Include the header only if you want it treated as part of the operation.
- Open the wizard. On the Windows ribbon, select Data > Text to Columns.
- Select a format. Choose Delimited or Fixed width, then select Next.
- Set the delimiter or break points. For Delimited, check the relevant delimiter and inspect the preview. For Fixed width, add or move break lines in the preview; double-click a line to remove it.
- Set the output and data formats. In the final step, check the column preview, choose formats where needed, and set the destination if the default would overwrite data.
- Select Finish. Check several resulting rows, especially records with unusual names, dates, blank fields, or punctuation.
The exact dialog can vary slightly with Excel updates, but the main choices remain the same. On Excel for Mac, the command is also on the Data tab as Text to Columns in supported desktop versions; ribbon placement and keyboard navigation differ from Windows. Excel for the web may not expose the full desktop wizard in every build. If the command is missing or its options are limited, open the workbook in desktop Excel or use a supported formula-based alternative.
Configure delimiters and fixed-width breaks
For a delimited file, select only the separator actually used in the source. A pipe-delimited record such as BX7|Blue mug|12.50 needs the Other option with a vertical bar entered as the delimiter. If the data uses tabs, choose Tab; do not assume that a screen display of spaces means the file contains ordinary spaces.
The checkbox labeled Treat consecutive delimiters as one can help when multiple separators represent a single boundary, such as several spaces between fields. Leave it off when repeated delimiters indicate an empty field. For instance, in North,,Active, the adjacent commas may mean that the middle value is blank. Treating consecutive delimiters as one can remove that empty position and shift the last value into the wrong column.
The Text qualifier setting matters for quoted data. In a comma-separated record such as 17,"Seattle, WA",Open, the comma inside the quoted location is part of the field, not a separator. A quotation mark as the text qualifier tells Excel to keep that quoted text together during a comma split. Inspect the preview rather than assuming the file uses standard quoting.
With Fixed width, Excel proposes breaks based on the preview, but you are responsible for deciding where each field ends. A break in the middle of a name or number will split that value. Fixed width is often a good fit for monospaced reports, but it is fragile when rows have varying-length fields or formatting that only makes them appear aligned.
Prevent overwritten data and conversion errors
Before finishing, look to the right of the selected source column. If those cells contain information, the output may replace it. Set a different Destination in the wizard, such as an empty area of the sheet, or copy the source data to a blank worksheet first. A destination must have enough empty columns for the widest row in the split.
Excel may interpret split values as numbers or dates when the column format is General. That can be helpful for a price field you plan to calculate with, but risky for identifiers. A code like 00127 may lose its leading zeros if converted to a number. Choose Text for ZIP codes, account codes, product IDs, and other values where every character matters. You can also select a column in the preview and choose a date format such as MDY when the source dates are genuinely month-day-year.
Regional settings can affect how Excel recognizes dates and decimal separators. A value such as 03/04/2025 is ambiguous without knowing whether the source means March 4 or April 3. Likewise, a comma may represent a decimal mark in one regional convention and a field delimiter in another. Use the wizard’s Advanced settings when available to specify decimal and thousands separators, and verify the result against the source rather than trusting appearance alone.
Text to Columns does not understand the meaning of each field. It can split at a chosen character, but it cannot reliably decide whether a comma is punctuation or a separator unless the data format and text qualifier clarify that. Keep an unchanged copy of important imported data until you have checked the split.
Examples and common problems
Suppose column A contains Chen, Mira in each row. Choose Delimited, select comma, and leave the text qualifier at its default unless the source calls for something else. The results are surname and given name, but a space after the comma may remain at the start of the second value. Check the output and clean it if needed. If names can contain commas or have different formats, a simple delimiter split may not produce reliable name fields.
For an address stored as 14 Pine Road, Unit 3, Albany, NY, splitting at every comma creates four fields, but the first field still combines the street number and street name. That may be exactly what you need, or it may not. Splitting at spaces instead would break the street name and city into many pieces. Decide what counts as a meaningful field before choosing delimiters.
- Everything stays together: Confirm that the intended delimiter is checked, that it actually appears in the selected cells, and that the selection includes the data.
- Values shift into the wrong columns: Check for empty fields and the consecutive-delimiters setting; confirm the selected text qualifier.
- Leading zeros disappear: Set the affected output column to Text in the wizard, then split again from the original text.
- Dates change or become unexpected numbers: Set the appropriate date format or Text before finishing, and confirm the source’s date order.
- Symbols do not split the text: Check for a different character, such as a nonbreaking space instead of an ordinary space, or use Other and enter the exact delimiter.
A frequent misconception is that Text to Columns only works with imported CSV files. It can split ordinary worksheet text too. Another is that choosing comma automatically handles every CSV correctly; quoted fields and regional number conventions can make the real structure more complicated, so the preview and a post-split check remain important.
Alternatives when the source keeps changing
For a one-time cleanup, Text to Columns is quick and visible. If you repeatedly receive refreshed files with the same structure, consider Power Query: import or connect to the source, split the column using a delimiter or position, and refresh the query when new data arrives. The exact connector and menu options depend on the source and Excel build, but a repeatable transformation is generally easier to maintain than re-running manual steps on every update.
For a formula-based split in Microsoft 365 or Excel 2024, TEXTSPLIT can return parts into adjacent cells and recalculate when the source changes. With the source string in A2, and commas separating each field, use:
=TEXTSPLIT(A2,",")
If A2 contains Mira Chen,Seattle,WA, the spilled result is Mira Chen, Seattle, and WA across three cells, provided those output cells are empty. This formula splits at every comma and does not by itself interpret quoted CSV fields, so it is not a complete replacement for a CSV-aware import process. If the output area is blocked, Excel may show a spill error. Text to Columns is also available in desktop Excel and can be more straightforward when you want to convert a selected range once.
Frequently Asked Questions
How do I split a full name into first and last names?
Select the name column, choose Data > Text to Columns, select Delimited, and use a space only if each name consistently contains exactly two parts. Names can include middle names, multiple surnames, or spaces, so review the output and consider a more tailored method if the data varies.
Can I split text at a character that is not listed?
Yes. In the Delimited step, select Other and enter the exact character, such as a pipe (|) or colon. Check the preview to confirm Excel is splitting at the intended character.
Will Text to Columns keep my original data?
It can overwrite cells to the right of the source column. Preserve the original by copying it first or setting a different destination in the wizard. Once the results are written over existing values, undo may help immediately, but a saved copy is safer.
Why did Excel remove the zeros at the start of my codes?
Excel treated the split values as numbers under the General format. Run the split again from the original text and set that output column to Text in the wizard. If the original has already been replaced and no backup exists, the missing zeros may not be recoverable from the numeric value alone.
Can Text to Columns split data automatically when new rows arrive?
No. It performs a worksheet transformation on the selected data rather than setting up an ongoing rule. Use a formula such as TEXTSPLIT where available, or a Power Query workflow, if you need to handle updated source data repeatedly.
Related
- Auto Flash Fill in Excel: How It Works and When to Use It
- Excel File Corrupted? How to Repair and Recover It
- Hide Columns in Excel: How to Hide and Unhide Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Geographical Heat Map: Create and Troubleshoot OneIn Excel for Microsoft 365 on Windows, select a column of named geographic regions and a column of…
- Archive Emails in Outlook to a PST FileTo archive Outlook emails in a PST file, use classic Outlook for Windows: go to File > Open…
- How to Import Contacts in Outlook From ExcelTo import contacts from Excel into Outlook, arrange each person on a separate row, save the worksheet as…
- Excel Merge Cells Button: Where It Is and How to Use ItIn Excel for Microsoft 365 on Windows, select the cells you want to combine, then choose Home >…
- AI Data Validation Tools: Uses, Limits, and How to ChooseAI data validation tools help teams check whether data is complete, consistent, correctly formatted, and suitable for analytics…
- Can You Open a Corrupted Excel File Online for Free?Sometimes you can open a corrupted Excel file online for free, but a browser-based viewer is not the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.