Make a workbook copy and reserve the destination columns first
Split a combined Excel column only in a workbook copy, and insert enough empty columns to hold every expected field before opening Text to Columns. Microsoft explicitly warns that distributed data can overwrite occupied cells to the right, so blank destination space is a prerequisite, not a cleanup step.
Suppose a supplier sends one column containing values such as SKU-104|M|Blue. The agreed structure is SKU, Size, and Color, so each source value should create three fields. If column B already contains Price and column C contains Supplier Note, splitting column A in place can replace information that has nothing to do with the combined code.
Keep the received workbook unchanged, then create a working copy with a clear name such as supplier-codes-split-working.xlsx. In the copy, insert three blank columns beside the source and label them SKU, Size, and Color. Keep the original combined column until reconciliation is complete. This layout gives the split a visible destination and leaves a source value beside every result.
Before changing the full list, identify several known rows: one ordinary value, one SKU with a leading zero, one missing segment, and one value with an extra pipe. These rows are your checks after the split; a clean-looking first record is not evidence that every supplier row follows the same contract.
Count irregular delimiters before running the wizard
Rows with the wrong number of separators should be isolated before the bulk split, because Text to Columns follows the delimiter it sees and does not know the supplier's intended field. An expected three-part value contains exactly two pipe characters.
In a temporary helper column, a formula such as =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")) counts pipes in A2. Fill it down in the working copy and filter for results other than 2. A row such as SKU-104|M is missing Color; SKU-104|M|Blue|Cotton has a fourth segment; and SKU-104||Blue has the expected delimiter count but an empty Size. Each needs a documented decision rather than an automatic shift.
Move questionable rows to a review section or flag them without changing their source text. Ask whether an extra pipe is valid data that should have been quoted or escaped, whether a missing value should remain blank, and whether the source system can produce a corrected export. Do not delete a delimiter merely to make the row fit three columns. That can turn a visible data-quality problem into a plausible but incorrect record.
Also check for spaces around separators. SKU-104 | M | Blue produces values with surrounding spaces, which can later break exact matching. Decide whether trimming is permitted by the data contract, then apply and document one rule consistently rather than silently changing selected rows.
Use Text to Columns with a preview and an explicit destination
On supported desktop editions, use Data > Text to Columns, choose Delimited, select the pipe as the delimiter, inspect the preview, and set the destination to the first blank output cell. Microsoft's current wizard page lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.
Select only the source data cells in the working copy, not the occupied columns beside them. In the wizard, clear delimiters that do not apply, choose Other, and enter |. The Data preview should show three fields for the known good row. On the final step, set Destination to the first cell under the new SKU heading rather than accepting a destination that replaces the source column.
Choose each output column's data format deliberately. A SKU such as 00127 is an identifier, not a quantity; set that output to Text when the leading zeros are meaningful. Size may also need Text when values include XS, M, or 10.5-US. Color is text. The wizard cannot infer the business meaning of these fields from their appearance.
Microsoft's adjacent-column guidance currently lists Excel for Microsoft 365, Excel 2024, and Excel 2021 and repeats the warning to keep enough blank columns on the right. Its broader desktop wizard page includes 2019 and 2016 as well. If the menu or destination box differs from these instructions, stop and use Excel's version-specific Help instead of guessing which cells the current interface will overwrite.
Use a version-aware fallback on the web or Mac
Do not assume that Excel for the web or Excel for Mac exposes the same desktop wizard. Microsoft states that Excel for the web does not have the desktop Text to Columns Wizard in the same form; its current web guidance uses Data > Split Text to Columns, followed by delimiter selection and Apply where that feature is available.
If your web interface lacks that command, TEXTSPLIT is a formula-based alternative only in the editions Microsoft currently lists: Excel for Microsoft 365 and Excel 2024, including their Mac editions. A formula such as =TEXTSPLIT(A2,"|") can spill the parts across columns, but the destination cells must still be empty. It also produces formulas, so preserve the source and decide whether the receiving workflow needs values rather than formulas before export.
Excel 2021, 2019, and 2016 are not listed on Microsoft's current TEXTSPLIT page. For those versions, use the documented desktop wizard or another approved import process. A missing function is a version limit, not a reason to paste an unverified formula from a newer interface.
On any platform, test a small copy first and make sure the output columns contain the same meanings. Menu parity does not guarantee data parity: delimiter count, text formatting, locale, and the destination range still control the result.
Reconcile the split before creating a separate CSV
The split is acceptable only after the new fields reconcile with the original combined values and the irregular rows have explicit outcomes. Compare the source and output in the working XLSX before producing an exchange file.
Use checks that expose quiet shifts:
- Recombine a sample as
SKU|Size|Colorand compare it with the original source value after applying only the approved space rule. - Confirm that leading-zero SKUs still contain every digit and were not converted into numbers or dates.
- Filter for blank Size or Color cells and trace each one back to the original combined value.
- Review the first, middle, and last records, plus every row previously flagged for too few or too many delimiters.
- Confirm that Price, Supplier Note, and other original adjacent columns still contain their known values.
Keep the .xlsx working copy because it preserves the original combined field, formulas, formats, and review notes. If the receiving system requires CSV, export a separately named file only after choosing the intended worksheet and columns. Reopen that CSV and repeat the known-record checks; saving successfully does not prove that identifiers, delimiters, or row scope survived as intended.
Preview the output without claiming it edits Excel
The CSV / Excel Online Preview can inspect a non-sensitive CSV, TSV, XLSX, or XLS selection, switch among sheets, search extracted rows, and show row and column counts. It is a review aid after the Excel work, not the place where Text to Columns runs.
The visible table renders at most 500 matching data rows, so sample the known records instead of treating the screen as a full-file audit. Search for a leading-zero SKU, a blank segment, and an item with a distinctive color. Compare the headers and field order with the destination system's import contract.
The previewer does not edit the workbook, insert blank columns, split cells, save formulas, or write changes back to the selected file. Its export button creates a separate data.csv from the full current sheet; it does not export only search matches and does not preserve workbook formulas, formatting, macros, or multiple sheets. Use Excel for the version-scoped split, then use the previewer only to inspect a separate output.
For a short redacted sample, the Text Diff tool can compare consistently sorted original -> expected and actual lines. It compares pasted text and cannot open an XLSX file, understand field types, or approve supplier data. Return to the workbook and source owner for every substantive correction.
Frequently asked questions
Can Text to Columns overwrite my Price column?
Yes. Microsoft warns that split output can overwrite adjacent data when there are not enough blank columns to the right. Work in a copy, insert the required destination columns first, and set an explicit destination in the wizard.
Which Excel versions have the desktop Text to Columns wizard?
Microsoft's current wizard page lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Web and Mac interfaces differ, so use their product-specific command or a supported formula fallback.
Why did one product create four columns instead of three?
That source value probably contains an extra delimiter, such as SKU|M|Blue|Cotton. Stop and ask what the fourth segment means. Deleting it or merging fields without a business rule can put a valid attribute in the wrong column.
Is TEXTSPLIT available in Excel 2021 or 2019?
Microsoft's current TEXTSPLIT page lists Microsoft 365 and Excel 2024 editions, not Excel 2021, 2019, or 2016. Use the desktop wizard in those older listed editions rather than assuming the newer function exists.
Can ToolboxHub split the column inside my XLSX file?
No. CSV / Excel Online Preview displays extracted spreadsheet data and can create a separate CSV from the selected sheet. It does not edit XLSX cells, run Text to Columns, or save changes back into the workbook.
Does a correct preview prove the full CSV is ready?
No. The visible table is capped at 500 matching rows, and preview cannot enforce business rules or destination types. Reconcile known rows, inspect irregular delimiter cases, preserve the XLSX, and test the receiving system with a controlled sample.