Excel’s Text to Columns wizard splits the contents of one column into separate columns. In Microsoft Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, then follow the wizard to split by a delimiter such as a comma or by fixed-width positions.
What the Text to Columns wizard does
Text to Columns is a built-in Excel tool for separating text that has been stored together in a single column. For example, it can turn a list of full names into first-name and last-name columns, or divide comma-separated location data into city and region fields. It changes the selected cells into multiple columns; it does not create a linked transformation that automatically updates when the original text changes.
The wizard offers two ways to identify where a split should occur:
- Delimited: Split wherever a selected character or group of characters occurs, such as a comma, tab, semicolon, space, or a custom symbol.
- Fixed width: Split at specific character positions, whether or not a delimiter is present.
It is useful when cleaning imported lists, preparing data for sorting or filtering, and separating fields copied from another system. Because the operation writes results into cells, it is important to review the destination and data format before completing the wizard.
Prepare the data before splitting
Start by making a copy of the source column or workbook if the original values matter. Text to Columns can overwrite cells to the right of the selected data, and the operation is not a substitute for a repeatable import process. Check that the worksheet has enough empty columns for the result. If a split creates three fields, for instance, the two columns immediately to the right of the source need to be clear or you should choose a different destination.
Select the actual cells you want to divide, excluding a heading unless you intend to process it too. A selection that includes a header may split the header along with the records. Also check whether the source data is truly consistent: a space delimiter can be troublesome when some names contain middle names, while a comma delimiter can be troublesome if commas occur inside quoted addresses or descriptions.
- Keep a backup when the source is difficult to recreate.
- Look for blank rows, inconsistent separators, and leading or trailing spaces.
- Decide whether identifiers such as account numbers should remain text so that leading zeroes are not lost.
- Identify an empty destination range before you start.
How to use the wizard on Windows
In Excel for Microsoft 365 on Windows, the standard process is:
- Select the cells in the column that contains the combined text.
- Open the Data tab and choose Text to Columns.
- In step 1, choose Delimited or Fixed width, then select Next.
- In step 2, configure the delimiter or column breaks. Use the preview to check how representative rows will be divided.
- Choose Next to set formats for individual output columns if needed.
- Set the destination if the default location is not suitable, then select Finish.
The default destination is usually the upper-left cell of the selected source range. Choosing a different destination preserves the original column and puts the split results elsewhere. If cells in the output area already contain data, Excel may warn that existing content will be replaced. Cancel and choose another destination if those cells should be kept.
Splitting by delimiters
Choose Delimited when a character marks the boundary between fields. The wizard lets you select common delimiters, including Tab, Semicolon, Comma, and Space, or enter another character in Other. You can choose more than one delimiter, but doing so means every selected character can create a split. For example, selecting both comma and semicolon splits at either character.
Suppose a cell contains Jordan Lee;Portland;Oregon. Selecting semicolon produces three fields: Jordan Lee, Portland, and Oregon. If the data is Lee, Jordan, selecting comma creates two fields, but selecting Space as well would also divide the name. The preview is the place to catch that difference before writing anything to the sheet.
The Treat consecutive delimiters as one option matters when repeated separators represent a single boundary. If a row contains North,,West, treating consecutive commas as one avoids an empty middle field. Leave it unchecked when consecutive delimiters are meaningful and an empty field should be preserved. The Text qualifier setting can also affect parsing: it identifies a character, commonly a double quotation mark, that encloses text so delimiters inside the enclosed text are not treated as field boundaries.
Splitting at fixed-width positions
Choose Fixed width when each field occupies a consistent number of characters, rather than being separated by a special character. In the preview, click to add a break line, drag a line to move it, or double-click a line to remove it. The preview shows the resulting columns, so inspect rows with the shortest and longest values before proceeding.
Fixed width can work well with consistently formatted codes or text reports, such as a file in which the first eight characters are an identifier and the next twelve represent a category. It is a poor fit when names or descriptions vary in length. A break at character 10 may divide one record correctly but cut a word in another. If column boundaries depend on variable spacing, clean or import the data with a more suitable method rather than relying on a single character position.
Choose column formats carefully
The wizard’s final step allows a format to be assigned to each output column. General lets Excel interpret values, which can be convenient for ordinary text and numbers but may change identifiers or date-like strings. Choose Text for values that must remain exactly as entered, especially codes with leading zeroes. Choose a date format when a split field represents a date and the source pattern is recognized correctly.
For example, an ID such as 00417 can become 417 if Excel treats it as a number. Setting that output column to Text before finishing helps preserve the zeroes. Similarly, a value such as 03/04/2025 may be interpreted differently depending on regional date settings: it could mean March 4 or April 3. Check the displayed result and the workbook’s expected date convention rather than assuming that a familiar-looking date was parsed as intended.
The Advanced button in the wizard provides settings for decimal and thousands separators. These are useful when importing numeric text that uses conventions different from the current Excel settings. For example, a source value may use a comma as the decimal separator and a period for grouping thousands. Set the separators to match the source so Excel does not misread the number.
Common problems and how to avoid them
- Data to the right disappears: The results are written into adjacent columns by default. Choose a blank destination or move existing data first.
- Leading zeroes vanish: Set the relevant output column to Text instead of General before finishing.
- Names split into too many pieces: Avoid using Space as a delimiter when names can contain middle names, titles, or multiword surnames.
- Some rows split differently: Check for inconsistent separators, extra spaces, or quoted text. The preview can reveal cases where the chosen rule does not fit every row.
- Dates or numbers change unexpectedly: Set column formats and regional separators deliberately, then verify the output against the source.
- Nothing seems to happen: Confirm that the selected range contains the combined values and that the output is not being placed somewhere unexpected.
After finishing, review several rows, including unusual or incomplete records. If the split produced unwanted columns, Ctrl+Z on Windows can undo the operation immediately, provided no later actions have made undo unsuitable. It is still safer to set the destination carefully and preserve a copy before making a large change.
Text to Columns compared with other approaches
Text to Columns is a quick, direct choice when a one-time split is all that is needed. Its main limitation is that the result is static: changing a source value later does not automatically rebuild the separated fields. It also works on a selected worksheet range, so it is less convenient for a recurring import that must be refreshed regularly.
For a repeatable transformation, Power Query can be a better fit. It can split a column by a delimiter or by positions as part of a query, and the transformation can be refreshed when the source is updated. Power Query is accessed from the Data tab, though the exact import and query options depend on the source and Excel build. It takes more setup than the wizard but is often preferable for recurring files or larger data-cleaning workflows.
Formulas are another option when the split should update as the source changes. In Microsoft 365 or Excel 2024, TEXTSPLIT can return separated values into adjacent cells. With the source string in A2, containing Mira Chen,Seattle, enter =TEXTSPLIT(A2,",") in B2; the result spills into B2 and C2 as Mira Chen and Seattle. This approach recalculates from A2, but it requires a supported Excel version and clear cells in the spill area. For a one-time cleanup without formulas, the wizard is generally simpler.
Mac and Excel for the web
Excel for Mac also provides Text to Columns on the Data tab, although the ribbon layout and wording can vary by release. The same core choices—delimited or fixed width, a preview, output formats, and a destination—apply. A Windows-specific Alt key ribbon sequence should not be assumed to work on Mac; use the visible Data tab command or the Mac version’s available menu and keyboard navigation.
Excel for the web may not expose every desktop control or behave identically across updates. If the command or a setting needed for your import is missing, open the workbook in the desktop application and use the Windows or Mac steps above. Keep in mind that the wizard changes worksheet contents, so saving a copy before working in a shared workbook is sensible.
Frequently Asked Questions
Where is Text to Columns in Excel?
In Excel for Microsoft 365 on Windows, select the source cells and go to Data > Text to Columns. On Mac, look for Text to Columns on the Data tab; the ribbon arrangement can differ by release.
Can Text to Columns split a full name?
Yes. Choose Space as the delimiter for simple two-part names. If names include middle names, titles, or multiword surnames, a space split may create too many fields, so inspect the preview and consider a more specific separator or a different method.
How do I keep leading zeroes when splitting data?
In the wizard’s final step, select the output column and choose Text. This tells Excel to preserve values such as 00417 rather than interpreting them as the number 417.
Does Text to Columns change the original data?
It writes the split results into worksheet cells and can replace content in the destination columns. By default, results begin beside the selected source range. Choose another destination or keep a copy if you need to retain the original values.
Can I make the split update automatically?
The wizard creates static results, not a live connection to the source cells. Use a formula such as TEXTSPLIT in a supported version of Excel, or consider Power Query for a repeatable process that can be refreshed.
Related
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
- Excel Text to Columns: How to Split Data Into Fields
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Do Superscript in Excel with a ShortcutTo make text superscript in Excel for Microsoft 365 on Windows, select the character or text, press Ctrl+1,…
- 0.3-Inch Hanging Indent in Word: Set It Up PreciselyTo create a 0.3-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph dialog, choose…
- ISNUMBER in Excel: How It Works and When to Use ItIn Excel, use =ISNUMBER(A2) to check whether the value in cell A2 is a number; the result is…
- How to Close Excel Using the Keyboard on Windows PCTo close Excel on a Windows PC, press Alt+F4 while the Excel window is active. If the workbook…
- How to Highlight Past Dates in Excel AutomaticallyTo highlight dates before today in Excel, select your date cells and create a formula-based conditional formatting rule…
- Excel Power Query: Get Data, Clean It, and Load It EasilyTo get data with Power Query in Excel for Microsoft 365 on Windows, open the Data tab, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.