In Excel, use Data > Text to Columns to split one cell into several columns, choosing a delimiter such as a comma or a fixed-width break. To split one cell into multiple rows, use TEXTSPLIT with a row delimiter in Microsoft 365 or Excel 2024, or split into columns first and transpose the results.
What Text to Columns does—and what it does not do
Text to Columns takes text in a selected column and separates it into neighboring columns. For example, it can turn a cell containing Jordan Lee,Seattle,WA into three cells: Jordan Lee, Seattle, and WA. The command is useful for imported lists, addresses, codes, and other data in which each record has several values joined in one cell.
The command does not directly split text downward into multiple rows. That distinction matters when a cell contains a list and each item needs its own record. In that case, use a formula, Power Query, or transpose the results after splitting. Decide whether you need columns or rows before changing the data: choosing the wrong orientation can make a list harder to analyze or combine with other tables.
Text to Columns changes the selected data in the worksheet, so preserve the original if you may need it later. The wizard writes its results into cells beside the source, rather than creating a separate copy of the entire worksheet.
Split a cell into columns with the wizard
In Excel for Microsoft 365 on Windows, prepare the worksheet and follow these steps:
- Put the text to split in one column. If there is a header, include it only if you want that header split too.
- Select the cells containing the text, then choose Data > Text to Columns.
- Choose Delimited if a character separates the values, or Fixed width if each value occupies a consistent position. Select Next.
- For delimited text, check the relevant delimiter—such as Tab, Comma, Space, or Semicolon—or enter a character under Other. Check the preview to see where Excel will split the text.
- Select Next to review column formats and the destination. Choose a format if needed, and set a destination that will not overwrite existing data.
- Select Finish, then inspect the results and check several rows for unexpected breaks.
The wizard also has a text qualifier setting, commonly a double quotation mark. A qualifier can keep a delimiter inside quoted text from being treated as a split point. For example, a comma inside a quoted address may be part of that field rather than a separator. The preview is a practical way to confirm whether the qualifier and delimiter match the structure of the source.
In desktop Excel for Mac, the command is also under the Data tab, though the dialog layout and keyboard shortcuts can differ. Excel for the web may not expose the same wizard controls in every context; if the command is unavailable, use a formula or open the workbook in desktop Excel.
Choose a delimiter or fixed-width split
A delimiter is a character or marker placed between values. Commas are common in comma-separated data, tabs often separate copied table data, and semicolons may be used when commas have another role in a regional data format. A space works for simple names such as Jordan Lee, but it is risky when a field can contain multiple words. Splitting on spaces would turn a full name into separate first, middle, and last-name pieces rather than one name field.
When more than one character separates values, use Other and enter the actual separator. For example, a pipe-separated record such as Jordan Lee|Seattle|WA should be split on the pipe, not on a comma. If there are repeated separators, review the option to treat consecutive delimiters as one. Turning it on can remove empty fields between repeated separators; leave it off when those empty fields represent meaningful missing values.
Choose Fixed width when characters begin and end at predictable positions, such as a legacy report with aligned fields. Excel displays a preview where you can add or move break lines. This works well when every row follows the same layout, but not when names or descriptions have variable lengths. For variable-length content, a real delimiter is usually more dependable.
Split text into rows instead
For a simple list inside one cell, TEXTSPLIT can return values vertically. This function is available in Microsoft 365 and Excel 2024. With the text red|green|blue in A2, enter this formula in B2:
=TEXTSPLIT(A2,,"|")
The cell layout is A2 containing red|green|blue and B2 as the formula cell. The empty second argument leaves the column delimiter unspecified; the pipe in the third argument is the row delimiter. The result spills down B2:B4 as red, green, and blue. Leave the cells below B2 empty so the result has room to spill. If a destination cell is occupied, Excel shows a spill error rather than replacing that content.
To split the same kind of text across columns instead, the cell layout is A2 containing red|green|blue and B2 as the formula cell. Use =TEXTSPLIT(A2,"|"); it returns red, green, and blue across the row. These formula results update if the source text changes, unlike a one-time wizard operation.
Another route is to run Text to Columns, copy the resulting horizontal cells, choose a blank destination, and use Paste Special with Transpose selected. Transpose changes rows to columns or columns to rows. It is handy for a one-off conversion, but the pasted result does not automatically update when the original text changes.
Use Power Query for repeatable imports
If the same kind of file arrives regularly, Power Query is often a better fit than repeating a manual split. In desktop Excel, turn the data into a table or select a cell in the source range, choose Data > From Table/Range, and confirm whether the table has headers. In the Power Query Editor, select the column, then choose Split Column > By Delimiter. Set the delimiter and, under advanced options, choose whether to split into columns or rows. Finish by using Home > Close & Load to return the transformed data to Excel.
A query records transformation steps, so a later refresh can apply the same split to updated source data. This is useful when importing monthly files or cleaning a repeated export. It takes more setup than the wizard, and the output is a query result rather than a direct edit of the source cells. Keep the source and the loaded result distinct so you know which one to update.
Choose the right method for the job
- Text to Columns: Best for a quick, one-time split into fields across a row, especially when the delimiter or field positions are clear.
- TEXTSPLIT: Best when you want a formula-based result that updates with the source and need either horizontal or vertical output.
- Transpose: Best for turning a small, already-split range sideways or downward without building a reusable transformation.
- Power Query: Best for repeated imports, larger cleanup tasks, or steps that need to be refreshed consistently.
Also consider how the result will be used. A table with one record per row is generally easier to sort, filter, summarize, and use in formulas than a list of several items packed into a single cell. If each item needs to be analyzed separately, splitting into rows may be the more useful final structure, even if splitting into columns is the quickest first step.
Prevent data loss and common splitting problems
Before running the wizard, check the cells to the right of the source. Excel may place several output columns there, and existing values can be overwritten. To avoid that, copy the source to an empty area or specify a blank destination in the wizard. You can undo a recent split with Ctrl+Z on Windows or Command+Z on Mac, but saving a copy first is sensible when the workbook contains important data.
Watch for automatic conversion. In the wizard, the General format can interpret text as numbers or dates. A code such as 00427 may become 427, and a date-like value may be converted according to Excel’s regional settings. Choose Text for identifiers, postal codes, and values whose exact characters matter. For dates, choose the appropriate date order when the source format is clear; otherwise, preserve the text until you can verify it.
Common issues include:
- Everything stays in one column: The selected delimiter may not match the source. Check the preview and try the actual separator, including a tab or semicolon.
- Words split into too many pieces: Space was selected even though fields contain spaces. Use a more specific delimiter or fixed width where appropriate.
- Blank output fields appear: Repeated delimiters can create empty fields. Decide whether those blanks are meaningful before enabling the option to treat consecutive delimiters as one.
- Leading zeros disappear: Excel interpreted the field as a number. Undo the operation and rerun it with the affected column set to Text.
- A formula result will not spill: Clear the cells in the intended output area and check for merged cells or other obstructions.
Finally, inspect the results rather than assuming every row follows the same pattern. A single missing delimiter, embedded comma, or extra space can shift values into the wrong columns and create errors that are difficult to notice later.
Frequently Asked Questions
Can Excel Text to Columns split data into rows?
No. Text to Columns places the split values in adjacent columns. For rows, use TEXTSPLIT with a row delimiter, transpose a split result, or use Power Query’s split-into-rows option.
How do I split a full name into first and last names?
If every name has exactly one space between first and last name, select the names and use Data > Text to Columns with Space as the delimiter. Names with middle names or multiple spaces may need a more careful method to avoid creating extra fields.
Can I split text automatically when a cell changes?
Yes, with a formula such as TEXTSPLIT in Microsoft 365 or Excel 2024. The wizard creates a one-time result, while a formula recalculates when its referenced source changes.
Why did Excel remove zeros from my split values?
The wizard likely treated the values as numbers under the General format. Undo the split and set that output column to Text before finishing, or preserve the source as text before splitting.
Can I split a cell by a line break?
Yes, but the delimiter must represent the line-break character, and the appropriate method depends on whether the output should be horizontal or vertical. In Power Query, choose the line-feed delimiter in the split settings; with formulas, use a line-break character as the delimiter in a supported formula and check the spill direction.
Related
- Excel Slicer: How to Change Its Data Source
- Excel Slicer: Change Font, Size, and Style in Excel
- Excel Slicer by Month: Set Up and Troubleshoot Date Filters
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Oracle SQL Text to Columns: Split Delimited DataTo split delimited text into separate columns in Oracle SQL, use INSTR to find delimiter positions and SUBSTR…
- How to Number Lines in Excel: 4 Reliable MethodsTo number lines in Excel, enter =ROW()-1 in the first numbering cell below a header in row 1,…
- How to Add a Logo to a Header in Word (Microsoft 365)To add a logo to a header in Word, open the header by double-clicking the top of a…
- How to Delete Subtotal Rows in Excel Without Losing DataTo remove subtotals created with Excel’s Subtotal command, click a cell in the list, choose Data > Subtotal,…
- Remove Duplicates in Excel: Shortcut Keys and StepsTo remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data and…
- Sorting in Google Sheets: A Practical GuideTo sort data in Google Sheets in a browser, select a cell in the table and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.