In Excel for Microsoft 365 on Windows, Text to Columns is best treated as a one-source-column tool: select a single column, then choose Data > Text to Columns and set its delimiter or fixed-width breaks. To split several source columns, repeat the wizard for each one, or use Power Query to split multiple selected columns with the same delimiter.
What Text to Columns Does
Text to Columns takes text stored in one cell or a column of cells and distributes parts of that text into neighboring columns. For example, it can turn a column containing entries such as Rina Patel into a first-name column and a last-name column, or divide North, 148, Active into three fields. It changes the layout of the data; it does not create a new worksheet or automatically remove the original source.
The common point of confusion is the word “multiple.” The command can create multiple output columns from one source column, but it is not a reliable way to ask the standard wizard to process several independent source columns at once. If a sheet has names in A and addresses in B, each column may need its own split unless you use a bulk method such as Power Query. Choose one source column for a predictable result.
Run Text to Columns on One Column
Before starting, check that the columns to the right of the source are empty or that you have chosen a safe destination. The wizard writes its results into cells, and existing content in the output area can be overwritten. These steps apply to desktop Excel for Microsoft 365 on Windows:
- Select the source column, or select the source cells you want to split. If the column has a heading, you can select the data below it to avoid treating the heading as a record.
- Choose Data > Text to Columns.
- In the Convert Text to Columns Wizard, choose Delimited when a character such as a comma or space separates fields. Choose Fixed width when fields line up at consistent character positions. Select Next.
- For a delimited split, check the appropriate delimiter, such as Tab, Comma, Space, or Semicolon. Use Other to enter a different single character. Review the preview to make sure the break points match the data.
- For fixed width, click in the preview to add a break line, or drag an existing break line to adjust it. Double-click a break line to remove it.
- Select Next if the wizard presents another step. Set the data format for each output column if needed, choose a destination if you do not want results to begin in the source location, and select Finish.
Excel for Mac also has a Text to Columns command on the Data tab, though the wizard’s appearance and ribbon arrangement can vary by release. Excel for the web may have a different set of data tools, so use desktop Excel if the command or options described here are not available in your browser version.
Choose Delimited or Fixed Width
Delimited is right when a particular character marks the boundary. A comma-separated list, a tab-separated export, and a pipe-separated log are all delimiter-based. If the source contains Patel, Rina, choose Comma. If it contains Patel; Rina, choose Semicolon instead. Selecting the wrong delimiter may leave the text intact or split it in the wrong places.
Fixed width is useful for imported reports where the first few characters always represent an ID, the next block a date, and the remaining characters a description. It depends on positions, not on a character between fields. It can be a poor fit when text lengths vary: names and descriptions of different lengths may shift the intended boundaries. Inspect several rows in the preview, not just the first one, before accepting the split.
A space delimiter needs special care. If names contain middle names or multiple spaces, splitting at every space can produce a different number of fields in different rows. Similarly, a comma inside a quoted address or description may not behave like a simple field separator in every type of file. When the data follows a formal CSV format with quoted fields, importing it through Excel’s text or CSV import workflow or Power Query is often safer than treating every comma as an ordinary delimiter.
Split Several Source Columns
For a few columns with straightforward, similar data, run Text to Columns once per source column. For example, suppose A contains full names separated by a space and B contains department codes separated by a hyphen. Split A using Space, check the results, and then split B using the hyphen as the other delimiter. The two source columns have different rules, so separate runs make the choices explicit and easier to verify.
Before each run, plan where the results will go. If A is split into two fields, its second field normally occupies B, which could destroy the original contents of B. You can avoid that collision by inserting blank columns, copying the source data to a separate area, or choosing an empty destination in the wizard. A safe workflow is to duplicate the worksheet first, especially when the source is important or the split affects many rows.
If many source columns all use the same delimiter, Power Query is usually more convenient. It can apply one split operation to multiple selected columns and preserve a repeatable set of transformation steps. If columns use different delimiters, select and split groups separately or apply a separate split operation to each column in the query.
Use Power Query for a Repeatable Bulk Split
Power Query is useful when the source is a recurring export or when several columns need the same treatment. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. If the data is not already an Excel table, confirm or adjust the range in the Create Table dialog. In the Power Query Editor, select the column headers to split; use Ctrl-click to select non-adjacent columns on Windows when appropriate.
Then choose Transform > Split Column > By Delimiter, select the delimiter, and choose how to split, such as at each occurrence or at the left-most delimiter. Check the preview, select OK, and use Home > Close & Load to return the transformed data to Excel. Menu details can vary somewhat across Power Query builds. On Mac, Power Query capabilities and interface options depend on the Excel release; if a command is missing, use a supported desktop build or split columns individually in the worksheet.
Power Query’s main advantage is repeatability: if the source table is refreshed or replaced with new rows, refresh the query to apply its recorded transformation again. It does not alter the original source cells in place; it loads a transformed result. That separation can be helpful for preserving raw imports, but remember to refresh the query after the source changes.
Use a Formula When You Want the Source Left Alone
In Microsoft 365 or Excel 2024, the TEXTSPLIT function can return separated values into neighboring cells while leaving the original cell intact. It is a good option for a small number of columns, for a live result that should update when the source changes, or when you want to test the split before replacing any data. It is not available in every older Excel version.
With the text red,green,blue in A2, enter =TEXTSPLIT(A2,",") in an empty cell such as C2. The result spills horizontally into three cells: red, green, and blue. The formula assumes A2 holds the source text and that the cells to the right of C2 are empty. If those spill cells are occupied, Excel shows a spill error until the obstruction is cleared. Copy the formula down for additional rows, ensuring each row has room for its result.
For a one-off split, the wizard may be simpler; for repeatable bulk processing, Power Query is often easier to maintain. A formula is most useful when you want the output to remain linked to its input. Do not place a formula’s spill range over original data, and remember that copying and pasting values is a separate step if you need a fixed, non-updating result.
Prevent Data Loss and Formatting Surprises
The most important safety check is the destination. Text to Columns can replace cells to the right of the selected source, so inspect that area before choosing Finish. If it contains data, select an empty destination in the wizard or make a copy of the source first. Undo with Ctrl+Z on Windows immediately after an unwanted split; on Mac, use Command+Z. Undo is less useful after intervening edits or after closing and reopening a file, so a backup is the more dependable precaution for valuable data.
Also review how Excel interprets the resulting fields. The wizard’s column data format options can help preserve values that look numeric but are actually identifiers. For example, splitting 00421,West and allowing the first field to become a number can turn 00421 into 421, losing the leading zeros. Set that output column to Text when the zeros are meaningful. Dates can also be interpreted according to regional settings; check ambiguous dates such as 03/04/2025 rather than assuming Excel has read them as intended.
Text to Columns is generally a value transformation, not a formula-building operation. If source cells contain formulas, splitting their displayed results does not preserve the original formula logic in separate output fields. Keep a copy of the original formulas if you may need them later. After splitting, verify a few short, long, blank, and unusual records, and check that the number of output columns is consistent with what the data actually contains.
Common Problems and How to Fix Them
- Nothing splits: Check that the selected delimiter actually appears in the source and that the correct delimiter box is checked. A semicolon is not the same as a comma, and a tab can look like a group of spaces.
- Too many fields appear: A delimiter may occur inside the text as well as between fields. Try a more suitable delimiter or use an import method that respects the source format.
- Parts of the next column disappear: The output overlapped existing cells. Undo if possible, then rerun with an empty destination or make room first.
- Leading zeros vanish or dates change: Set the relevant output column’s format in the wizard, or preserve the values as text when they are identifiers rather than quantities.
- Rows split inconsistently: Some records may contain extra delimiters or variable spacing. Inspect the source and decide whether those characters are true field boundaries before processing the whole column.
Frequently Asked Questions
Can I use Text to Columns on multiple columns at once?
For a dependable result, treat the standard wizard as a one-source-column operation and run it separately for each source column. To split several selected columns with the same delimiter in one repeatable workflow, use Power Query’s Split Column command.
How do I split a column into two columns in Excel?
Select the source column, choose Data > Text to Columns, select Delimited or Fixed width, set the break rule, review the preview, and choose an empty destination if nearby cells contain data. Then finish the wizard.
Can I split text at a space without losing the original?
Yes. Set a separate destination in the wizard or use a formula in an empty area. In Microsoft 365 or Excel 2024, TEXTSPLIT can return the parts while leaving the source cell unchanged.
Why did Text to Columns overwrite my data?
The output was written into cells that already contained content, usually to the right of the source. Undo promptly, then rerun the split with a clear destination or create a backup copy first.
Does Text to Columns update when the source changes?
No. It writes the split result as cell values and does not maintain a live connection to the original text. Use a formula for linked results or Power Query when you want to repeat the transformation after refreshing data.
Related
- How to Partially Protect an Excel Sheet
- How to Print Just a Highlighted Area in Excel
- How to Merge Cells in Excel Without Losing Data Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Real Estate Profit and Loss Excel Template: A GuideA real estate profit and loss (P&L) template in Excel is a worksheet that records property income and…
- Excel Shortcut to Select a Column on Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Spacebar to select the entire column containing the active cell.…
- How to Wrap Text in Google Sheets on Desktop and MobileTo wrap text in Google Sheets, select the cell or cells, then choose Format > Wrapping > Wrap.…
- How to Find Duplicate Entries in ExcelTo find duplicate entries in Excel, select the cells you want to check, then choose Home > Conditional…
- Print Labels in Word from Excel: Step-by-Step GuideTo print labels in Word from Excel, prepare your address list with one person or destination per row,…
- Excel Column to Comma-Separated List: Easy MethodsIf your values are in A2:A20, enter =TEXTJOIN(", ",TRUE,A2:A20) in an empty cell to turn the column into…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.