When a system exports five employees as “Abena Mensah, HR, Manager, 01-Jun-2021, 5800” crammed into a single cell, that data is practically useless until it’s separated. You can’t sort by department, filter by hire date, or calculate an average salary when everything lives in one column. Text to Columns in Excel turns that wall of joined data into individual, workable fields in about thirty seconds.
The dataset behind this guide contains three realistic scenarios: comma-separated employee records including names, cities, emails, and phone numbers; space-delimited order codes that need splitting into prefix, year, and region ID; and fixed-width records exported from a legacy payroll system where fields are identified by character position rather than any delimiter. Each scenario demonstrates a different mode of the same tool, and each reveals a specific mistake that corrupts the output if you’re not careful.
Text to Columns is available in Excel 2007 and all later versions, including Excel 2016, 2019, 2021, and Microsoft 365. The newer TEXTSPLIT function (Microsoft 365 only) offers a formula-based alternative for users who need a dynamic, non-destructive split — but the wizard-based method described here works universally.
Quick Takeaways:
- Always copy your raw data to a new column or sheet before splitting. Text to Columns overwrites in place. If you save after running it, there is no undo.
- Check the Data Preview in Step 2 of the wizard before clicking Next. If the preview looks wrong, the delimiter is wrong.
- Set column format to Text in Step 3 for phone numbers, IDs, and postal codes. Excel strips leading zeros from numeric-looking text if you leave format as General.
- Dates often convert to serial numbers (like 45366) after splitting. Select the date column in Step 3 and choose Date format before finishing.
- Insert blank columns to the right of your data before splitting. Without them, the split result overwrites whatever data already sits in those columns.
How to Use Text to Columns in Excel
Opening the wizard
Select the column containing your combined data. Go to Data > Text to Columns. The Convert Text to Columns Wizard opens in three steps. You’ll choose between Delimited (for data separated by a specific character) or Fixed Width (for data aligned by character position), configure the split, set column formats, and click Finish.
The whole process takes under a minute on clean data. The challenge isn’t the steps — it’s knowing which options to choose at each stage, and which traps to avoid.
Splitting by Delimiter: Comma-Separated Data
The first section of the dataset contains five employee records where every piece of information is joined with commas. Row two, for example, holds “Accra, Ghana” in one cell, “abena.m@company.com, +233209988776” in another, and “HR, Manager” in a third. A single run of Text to Columns on each of those columns separates them cleanly.
Step 1: Choose Delimited
With your column selected, open Data > Text to Columns. On the first screen, select Delimited and click Next.
Step 2: Choose your delimiter
The wizard shows a list of common delimiter options: Tab, Semicolon, Comma, Space, and Other. For the employee data, tick Comma. The Data Preview at the bottom of the dialog immediately updates to show the split result. This preview is the most useful part of the entire wizard — I always check it before clicking Next, and it’s saved me from at least a few wrong-delimiter mistakes.
One thing to watch: if any fields in your data contain a comma as part of the content (like “Cape Town, South Africa” where the city includes the comma), splitting by comma will break “Cape Town” and “South Africa” into separate cells too. The dataset handles this by splitting city and country into separate columns intentionally — but if you didn’t want that split, you’d need to clean the data first.
Step 3: Set column formats before finishing
This step is critical and easily overlooked. After choosing the delimiter, you land on a screen showing each resulting column with a format option. General is selected by default, which works for most text and numbers. But for specific data types, you need to change it.
The phone number column in the dataset contains “+233244001122” — a number that starts with a country code. If you leave the format as General, Excel may interpret this as a number and process it numerically. More dangerously, formats like “00233244001122” with leading zeros will lose those zeros instantly. Set that column to Text in Step 3 to preserve every character exactly as it appears.
For Microsoft’s full documentation on Step 3 format options, the official Text to Columns reference covers every format code available in the wizard.
Splitting by Space: Order Code Data
The second section of the dataset uses space as the delimiter. Each order code like “ORD 2024 GH001” needs splitting into three parts: the prefix (ORD), the year (2024), and the region-ID (GH001). The product descriptions work similarly — “LAPTOP HP 15-DA” splits into product type, brand, and model.
Handling multiple spaces and combined fields
The shipping column “DHL EXPRESS ACCRA” splits into three fields: courier, service type, and location. That’s useful. But “2024-01-15 899.99” (date and amount in one cell separated by a space) produces “2024-01-15” and “899.99” — exactly what’s needed.
The complication with space-delimited data is over-splitting. “CAPE TOWN” split by space becomes “CAPE” in one column and “TOWN” in the next. The clean version of the dataset handles this by accepting that Cape Town becomes two columns — something to plan for upfront by either pre-processing the data or using a fixed-width approach if positions are predictable.
The “Treat consecutive delimiters as one” option
One option in Step 2 that frequently saves time: Treat consecutive delimiters as one. When source data has double spaces, double commas, or any run of repeated delimiters, each one normally creates a blank column between the filled ones. Ticking this box collapses them. For the order code data — where spacing is consistent — it’s unnecessary. But for irregular CSV exports, it’s often essential.
Splitting Fixed-Width Data: Legacy System Records
The third section demonstrates fixed-width splitting — the mode most people overlook because it requires no delimiter at all. The legacy payroll record “E001 John Doe SALES 004500” packs four fields into a single string using character positions: characters 1-4 are the employee ID, 5-20 are the name (padded with spaces), 21-27 are the department, and 28-33 are the salary.
Using the fixed-width ruler
Choose Fixed Width in Step 1. Step 2 shows a ruler above a preview of the data. Click anywhere on the ruler to place a break line — a vertical divider that tells Excel where to cut. Drag existing break lines to move them. Double-click to delete one. For the payroll record, place breaks at positions 4, 20, and 27.
The off-by-one mistake is the most common error here. If you place the break at position 5 instead of 4, the ID picks up the first character of the name. A monospaced font view helps enormously — every character occupies the same width, so you can count exactly. The dataset notes: “Count characters in a monospaced font view for accuracy.” That’s the practical advice the wizard’s own tooltip misses.
The Mistakes That Corrupt Your Results
Ten errors are documented in the dataset’s third sheet. Three of them are worth calling out specifically because they cause permanent data loss or silent incorrect results.
Dates become serial numbers
After splitting a date column, cells that showed “2024-03-15” may display “45366” — Excel’s internal date serial number. This happens because the split column defaults to General format, and Excel interprets the result as a number. Fix it by selecting the date column in Step 3 and setting the format to Date: YMD before clicking Finish. Trying to reformat after the fact is possible but adds steps.
Leading zeros vanish silently
Phone numbers in the dataset include “+233244001122” — and some systems export phone numbers as “00233244001122” with leading zeros. Set to General format, Excel sees a long number and drops the leading zeros immediately. The result is 233244001122. No error, no warning. Just wrong data. Set the column to Text in Step 3 for every column containing phone numbers, employee IDs, postal codes, or any other field where leading zeros carry meaning.
Splitting overwrites existing data without asking
This is the one that stings most. Text to Columns writes its output starting from the selected column and extending right. If columns B through E already contain data, that data is gone. Excel does warn you, but it’s easy to click through the warning when you’re moving fast.
The habit to build: before running Text to Columns on any column, insert enough blank columns to the right to accommodate the split. For “Accra, Ghana” splitting into two parts, insert one blank column to the right before starting. For five-field comma-delimited data, insert four. This takes ten seconds and prevents overwrites entirely.
For more on the TEXTSPLIT function — a formula-based alternative that splits data without destroying the original — Microsoft’s TEXTSPLIT reference explains how it works in Microsoft 365.
A Workflow That Protects Your Data
Copy your raw column to a new location. Insert blank columns to the right. Run Text to Columns on the copy. Check the preview in Step 2. Set column formats in Step 3 — Text for IDs and phones, Date for dates, General or Number for amounts. Click Finish. Verify the output.
The full employee split from the dataset — turning “john.doe@company.com, +233244001122” into a clean email column and a clean phone column, or “Sales, Senior Executive” into department and job title — takes about 90 seconds when you know the steps. The time you save downstream on sorting, filtering, and formula-building justifies learning this process thoroughly.