Yes—Excel’s Text to Columns tool has advanced controls for choosing delimiters or fixed-width breaks, handling quoted text, setting each output column’s data type, and selecting where the split results go. In Excel for Microsoft 365 on Windows, select the source cells and choose Data > Text to Columns, then work through the wizard’s preview before you finish.
What the advanced options are for
Text to Columns is more than a quick way to divide a list at commas. Its wizard lets you control how Excel recognizes boundaries and interprets the pieces. Those choices matter when a field contains punctuation, when a date could be read in more than one way, or when an identifier begins with zeroes that must not disappear.
The tool is useful for cleaning imported or pasted data, such as a column containing full names, product codes, tab-separated records, or address components. It changes the selected cells into multiple columns; it does not create a permanent connection to the original data. If you import a new file later, you may need to repeat the operation or use a repeatable method such as Power Query.
The wizard offers two ways to define the split:
- Delimited: divide text wherever a chosen character, such as a comma, tab, semicolon, or space, appears.
- Fixed width: divide text at positions you mark in the preview, regardless of which characters appear there.
Prepare the worksheet before splitting
Text to Columns writes its output into the source column and the columns to its right unless you specify another destination. Before starting, check that those cells are empty or that you are comfortable replacing their contents. For example, splitting a value in A2 into three fields can fill A2, B2, and C2. A populated B or C column is at risk, so make a copy of the data or choose a clear destination first.
It also helps to identify what each field represents. A postal code, employee ID, or account number is usually a label, not a number to calculate with. A date should be a real Excel date if you intend to sort or calculate with it. Knowing the intended type makes it easier to choose the correct column format in the final wizard step.
For a small, one-time cleanup, a duplicate sheet is often sufficient protection. For a large import or a workbook with important formulas, save a separate copy before changing the data. Text to Columns is a transformation, not just a visual display setting.
Open the wizard and choose a destination
On Windows, select the cells you want to split and use Data > Text to Columns. The wizard first asks whether the data is delimited or fixed width. Choose the option that matches the structure of the text, not merely the character you happen to see. A comma-separated record is delimited; a report where values always occupy the same character positions is a better candidate for fixed width.
After choosing the split method and adjusting its settings, inspect the preview at the bottom of the wizard. It shows where Excel plans to place the breaks, helping you catch an incorrect delimiter or misplaced boundary before writing anything to the sheet. In the final step, use the Destination box to set the top-left output cell. For example, choosing a blank cell such as E2 keeps the original value in column A while the split results begin in E2.
Excel for Mac also provides Text to Columns from the Data tab, though the wizard’s appearance and keyboard navigation can differ from Windows. These instructions describe the Microsoft 365 desktop workflow; ribbon key tips and some interface details are not identical on Mac.
Configure delimiters and text qualifiers
In the Delimited step, select the character or characters that mark field boundaries. Common choices are Tab, Comma, Space, and Semicolon. Choose Other to enter a different single character. The preview updates as you change the selections, so use it to confirm that the fields land in the intended columns.
The Treat consecutive delimiters as one option is useful when repeated separators mean only one boundary. Consider a list with several spaces between words: with this option selected, a run of spaces can act like one separator. Leave it cleared when each delimiter represents a meaningful empty field. For example, in comma-separated data, two commas in a row may mean that a value is missing; collapsing them would remove that empty position and shift later values.
The Text qualifier setting tells Excel which quotation mark can enclose a field. A quoted field can contain a delimiter without being split at that delimiter. If a record contains "Smith, Jane",00421,North, choose Comma as the delimiter and a double quote as the text qualifier. The comma inside the quoted name is treated as part of the name, while the commas outside the quotes separate the fields. If the qualifier is set incorrectly or to None, the name may be divided at its internal comma.
Qualifiers are especially important with exported CSV-style text, but they do not repair every malformed file. If quotation marks are missing or inconsistent, inspect the preview and the source data; a wizard setting cannot reliably infer the intended structure from broken records.
Use fixed width when positions define the fields
Choose Fixed width when records follow a consistent character layout and do not rely on a separator. In the preview, click where a field should end to add a break line. You can adjust a break by dragging it, and remove an unwanted break by double-clicking it. The preview helps reveal whether the proposed columns line up across the selected rows.
For example, a legacy report might place a six-character code in positions 1–6, a department name in positions 7–22, and an amount after that. Fixed width can separate those fields even if the department names contain spaces. It is less suitable when names have variable lengths and there is no consistent position for the next field. In that case, a delimiter or a more structured import method is usually safer.
Watch for rows with shorter or longer text than the sample shown in the preview. A break that works for most records might divide an unusual row incorrectly. Check representative rows, including the longest values and any records with blank fields, before accepting the split.
Set the data format for each output column
The final wizard step lets you select an output column in the preview and choose its data format. General asks Excel to interpret values automatically. Text keeps the contents as text, which is appropriate for codes, numbers with leading zeroes, and values that should not be converted. Date lets you specify an order such as MDY, DMY, or YMD. You can also choose not to import a selected column if it is not needed.
Suppose the source contains 00421. If Excel interprets that field as a number, the leading zeroes can be dropped and the result becomes 421. Set that output column to Text when the zeroes are part of the identifier. Changing a cell’s number format to display zeroes is not always equivalent: a text identifier is not the same kind of value as a number formatted to show extra digits.
Date interpretation deserves particular care. A string such as 03/04/2026 could represent March 4 or April 3, depending on the date convention. Select the date order that matches the source, rather than relying on Excel’s regional defaults. If the source uses an ISO-style date such as 2026-11-03, choose YMD when the wizard’s date options are available and check the result in the worksheet. A real date can be sorted chronologically and used in calculations; a text string that only looks like a date may not behave the same way.
For mixed records, set formats column by column. For instance, a split containing a product code, a quantity, and a date may need Text for the code, General for the quantity, and the appropriate Date order for the date field.
Example: split a quoted contact record safely
Imagine A2 contains "Lee, Morgan",00718,2026-11-03. The intended fields are a name, an ID, and a date. Select the cell, start Text to Columns, choose Delimited, and select Comma. Set the text qualifier to a double quote so the comma inside the name does not create an extra field. In the preview, confirm there are exactly three columns.
In the final step, select the ID output column and choose Text to preserve 00718. Select the date column and choose Date with YMD if that matches the source’s ordering. Set the destination to a blank area if the original record should remain intact, then finish and review the output. The expected result is the name Lee, Morgan, the text ID 00718, and a date representing November 3, 2026. This example shows why a correct delimiter alone is not enough: the qualifier and data formats determine whether the split preserves the meaning of each field.
Common problems and how to prevent them
- Everything goes into one column: the selected delimiter may not match the source. Check whether the data uses tabs, semicolons, or another character, and verify the preview.
- Names or descriptions split in the wrong place: set the appropriate text qualifier when fields are quoted, or use a delimiter that actually separates the records.
- Leading zeroes disappear: set identifier columns to Text in the final step before finishing.
- Dates switch month and day: select the source’s date order explicitly and verify a few results.
- Values overwrite nearby data: choose a blank destination or clear the intended output area before running the wizard.
- Rows have different field counts: inspect the source for inconsistent delimiters, missing values, or unmatched quotes. Text to Columns cannot determine the intended structure of every irregular record.
If the results are wrong, undo immediately with Ctrl+Z on Windows, or Command+Z on Mac, then correct the settings and try again. Undo is most reliable before making other changes to the workbook. For valuable data, keeping an untouched copy is safer than depending on undo history.
When to use another Excel tool
Text to Columns is a good fit for a one-time split of a clean, consistent column. It is direct and gives you control over delimiters, qualifiers, formats, and destination. It is not the best choice for a recurring process where new files arrive regularly or where transformations must be refreshed and documented.
Power Query, available through Excel’s Data tools, is often better for repeatable imports and cleanup. A query can split a column by delimiter, set data types, and be refreshed when the source changes. Its interface takes more learning than the wizard, but it can avoid repeating manual steps each time updated data arrives.
Flash Fill can help when you want Excel to infer a pattern from examples, such as extracting first names from a list. It is less dependable when the source has inconsistent patterns, and its results are filled values rather than a refreshable transformation. Excel for Microsoft 365 also has functions such as TEXTSPLIT for formula-based splitting, but Text to Columns may be simpler when you want to convert a selected column directly and do not need a formula-driven result.
Frequently Asked Questions
Can Text to Columns split text into more than two columns?
Yes. A selected delimiter can occur multiple times, producing several output columns. Check the preview and make sure the destination area has enough empty columns for every field.
How do I split a cell by a space without breaking full names?
Select Space as the delimiter only if every space marks a boundary. If full names should stay together, a simple space split will separate them too; use a more reliable delimiter, a fixed-width pattern, or another method that matches the data structure.
Why does Excel remove zeroes from my codes?
Excel may recognize a code made only of digits as a number. In the wizard’s final step, select that output column and choose Text so leading zeroes are retained as part of the value.
Can I keep the original column after splitting?
Yes. Set Destination to a blank cell or range elsewhere in the worksheet before finishing. The original values can remain in place while the split fields are written to the new location.
Does Text to Columns update automatically when the source changes?
No. It applies a one-time change to the selected data. For a repeatable process, consider Power Query, which can save transformation steps and refresh them against updated source data.
Related
- Excel Remove Slicer: How to Delete or Clear One
- Excel Remove Everything but Duplicates: Keep Repeats
- Excel Remove Duplicates Not Available? Fixes to Try
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting Rows in Excel: A Practical GuideTo conditionally format an entire row in Excel, select the rows and columns you want formatted, choose Home…
- Why Duplicate Values Aren’t Working in Microsoft ExcelDuplicate values usually seem not to work in Excel because the rule is looking at the wrong range,…
- Excel Opposite of Text to Columns: How to Combine DataThe opposite of Excel’s Text to Columns command is combining data from separate columns into one cell. Use…
- How to Hide Long Text in Excel Cells Without Deleting ItTo keep long text from showing across neighboring cells in Excel, make the cells to its right non-empty,…
- Excel DOS Keyboard Shortcuts: What Actually WorksYes—modern Microsoft Excel has many keyboard shortcuts, including shortcuts for editing cells, navigating worksheets, and formatting data. If…
- Excel PivotTable Testing: Checks, Steps, and Common FixesTo test an Excel PivotTable, compare its results with a small source dataset whose totals you can verify…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.