Excel Tutorials
One column holding two pieces of information is one of the most common problems in any spreadsheet, and Text to Columns is the three-click wizard that fixes it. This lesson covers exactly where to find it, delimited versus fixed width, the format step that decides whether your leading zeros survive, and the hidden uses that have nothing to do with splitting at all. It uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Text to Columns takes the contents of a single column and splits it across several columns. A cell holding Smith, John becomes one cell with Smith and another with John. A product code like EVT-2026-0041 becomes three separate fields you can sort and filter independently.
It exists because data almost never arrives in the shape you need it. Exports jam a full name into one field, systems concatenate a code out of three parts, and a CSV that opened badly puts an entire row into column A. Splitting that by hand is not realistic past about ten rows, and Text to Columns handles ten thousand in the same three clicks.
The wizard runs in three steps: choose how the data is separated, specify the separator, then set the format for each resulting column. That third step is the one people rush, and it is where leading zeros get destroyed and dates get read in the wrong order. It is also worth knowing that the tool has a second life as the fastest way to convert text that looks like numbers into numbers Excel will actually add up, covered further down.
Step-by-step, matching the video above.
Go to the Data tab on the ribbon and look in the Data Tools group, roughly in the middle. The button is labelled Text to Columns and sits directly beside Flash Fill, with Remove Duplicates and Data Validation just to its right. On a narrow window Excel collapses that group into a single Data Tools button you have to click first to reveal it. The keyboard route is Alt, A, E, pressed one key after another rather than held together, and on Excel for Mac it is Data > Text to Columns in the same position.
Click the column letter to select the whole column, or highlight just the cells you need. Text to Columns only accepts one column at a time, so if you select two the button does nothing and you get an error explaining it. Leave the header row out of the selection unless you want it split too.
This is the step almost everyone skips. The wizard writes its results into the columns to the right of your source data and does not create space for them, so it silently overwrites whatever is already there. Splitting into three parts means you need two empty columns beside the original. Right-click a column letter and choose Insert, twice.
Delimited splits wherever a chosen character appears and is the right answer for nearly all modern data: CSV exports, names separated by spaces, codes joined with hyphens. Fixed width splits at character positions you set by hand, which is for older system printouts where the columns line up visually but there is no separator character at all.
The checkboxes offer Tab, Semicolon, Comma, Space and Other, where you can type any single character such as a hyphen or pipe. You can tick more than one at a time. The Data preview pane at the bottom updates live and shows the split lines, so confirm it looks right here rather than after you finish.
If your data is padded with several spaces between values, leaving this unticked creates an empty column for every extra space. Ticking it collapses a run of delimiters into a single split. The Text qualifier dropdown beside it tells Excel to ignore delimiters that sit inside quotation marks, which is what stops a comma inside "Smith, John" from splitting that name in half.
Click a column in the preview, then choose its Column data format. General converts anything that looks numeric into a number, Text keeps it exactly as typed, and Date lets you specify the day and month order. This is where leading zeros and misread dates are won or lost, and it cannot be fixed afterwards without re-splitting.
Select a column in the preview and choose Do not import column (skip) to discard it entirely. Useful for stripping a junk field out of an export without having to split it and then delete it.
The Destination box defaults to the original cell, which is what overwrites the neighbouring columns. Change it to an empty cell such as $H$2 and the results land there instead, leaving your source data completely untouched. Click Finish and the split happens instantly, with no further prompts.
The button has stayed in the same place for years, but the route differs slightly by platform.
Data tab → Data Tools group → Text to Columns. Keyboard: press Alt, then A, then E in sequence.
Data tab → Text to Columns, in the same Data Tools area. The wizard itself is identical to the Windows version, three steps and the same options.
Text to Columns is not available in the browser version. You need the desktop app, or you can use the TEXTSPLIT function instead if you have Microsoft 365.
Excel collapses the whole Data Tools group into one small button. Click Data Tools and the menu drops down with Text to Columns at the top.
You have either selected more than one column, or you are inside a cell in edit mode. Press Escape, select a single column, and it becomes available again.
| Original Cell (A2) | Wizard Settings | Result |
|---|---|---|
| Smith, John | Delimited → Comma | Smith | John |
| John Andrew Smith | Delimited → Space | John | Andrew | Smith |
| EVT-2026-0041 | Delimited → Other: - | EVT | 2026 | 0041 |
| "Smith, John",Gold | Comma + Text qualifier " | Smith, John | Gold |
| 00417 | General format | 417 (zeros lost) |
| 00417 | Text format | 00417 (zeros kept) |
The last two rows are the same input with one setting changed in step 3, and they show why that step matters. Row four is the text qualifier doing its job: without it, the comma inside the quoted name would split Smith and John into separate columns and push the membership tier out of place. Row three is worth noting too, because an event code split on hyphens gives you a year column you can now actually filter and sort by.
For data with no separator at all, where the columns simply line up.
Step 2 changes completely: instead of delimiter checkboxes you get a ruler and a preview of your data in a monospaced font, with the character positions marked along the top.
Each click adds a vertical arrow where the split will happen. Double-click a line to delete it, and drag to reposition one. Excel usually guesses a few break points for you, and they are often in the wrong place.
The preview only shows the first few rows. A value that is longer than the ones on screen will be cut in the wrong place, so scroll the preview pane down to check the widest entries before clicking Finish.
Reports printed from older accounting, payroll or ERP systems are the classic case: everything lines up neatly in columns because it was formatted for a fixed-width printer, but there is no comma or tab anywhere in the file.
Text to Columns does three other jobs that have nothing to do with splitting anything.
Numbers imported as text will not sum. Select the column, open Text to Columns and click Finish immediately without changing a single setting. Excel re-evaluates every cell on the way out and converts them into genuine numbers. It is the fastest fix there is for a SUM that returns zero.
A column of dates stuck as text can be repaired by running the wizard and setting the Date format with the correct day and month order in step 3. This handles imports where 03/04/2026 was read as the wrong one of March and April.
Splitting on Space with Treat consecutive delimiters as one ticked cleans up padded values, though TRIM is usually simpler if you just want the spaces gone and no split at all.
Excel remembers your last Text to Columns settings and applies them to anything you paste for the rest of the session, which is why pasted text suddenly starts splitting itself. Run the wizard once on any cell with every delimiter unticked, click Finish, and normal pasting resumes.
Three ways to split data, each right in different circumstances.
It works in every version of Excel including very old ones, handles the whole column in one go, and gives you explicit control over formatting. But the result is static: change the source data and the split columns do not update.
Type the first result manually in the next column and press Ctrl+E. Excel infers the pattern and fills the rest. Excellent for irregular data that has no consistent delimiter, less reliable when the pattern varies part way down the list.
=TEXTSPLIT(A2," ") spills the result across columns and recalculates whenever A2 changes. It is the best option for data that gets refreshed, but it requires Microsoft 365 and will show #NAME? for anyone opening the file on an older version.
If the same messy file lands every month, Data > Get & Transform lets you record the split once and re-run it on each new file with a single refresh, which beats reopening the wizard every time.
The split writes into the columns to the right and overwrites them without asking. Insert enough empty columns before you start, or set the Destination box to a clear area of the sheet.
Zip codes, employee IDs and product codes starting with a zero get converted to plain numbers, and 00417 becomes 417. Set that column to Text in step 3 of the wizard, before you finish.
A comma inside a quoted value like "Smith, John" will split that name into two columns and knock every field after it out of alignment. Set the Text qualifier dropdown to a double quote.
Middle names, two-word surnames like Van Dyke, and suffixes such as Jr all produce more columns than you planned for, and the extras land in whatever is beside them. Scan the preview and sort by length first.
The break lines are set based on the first few visible rows. A longer entry further down gets sliced in the wrong place, and you will not see it until after you have clicked Finish.
Once you have run the wizard, Excel applies the same split to text you paste afterwards, which looks like Excel randomly breaking your data. Run it once with all delimiters unticked to reset it.
Text to Columns only handles a single column of source data. With two selected the command refuses to run, which is easily mistaken for the feature being broken.
The result is a static one-off. If the underlying data gets refreshed regularly, use TEXTSPLIT or Power Query instead so the split re-runs on its own.
If you are splitting one column into three every month, the export is fighting you.
Free 14-day trial. No credit card required.
It is on the Data tab, in the Data Tools group, between Flash Fill and Remove Duplicates. The keyboard sequence Alt, A, E opens it in every recent version of Excel for Windows.
It splits the contents of one column into several columns, either at a chosen separator character such as a comma or space, or at fixed character positions you set manually.
Delimited splits wherever a specific character appears, which suits CSV exports and names separated by spaces. Fixed width splits at character positions you draw yourself, which suits old system reports where columns line up but have no separator.
The split writes into the columns to the right of the original and does not insert space for them. Insert enough blank columns first, or set the Destination box in step 3 to an empty area of the sheet.
Ctrl+Z reverses it while the workbook is open, but if it has already overwritten a column and you have saved and closed the file, that data is gone. Work on a copy for anything important.
In step 3 of the wizard, select the column in the preview and set its Column data format to Text. Leaving it on General converts 00123 into the number 123 and drops the zeros permanently.
Excel guessed the wrong day and month order. In step 3, set the column format to Date and choose the order that matches your source data, such as DMY for European dates, rather than leaving it on General.
Choose Delimited, tick Space as the delimiter, and run the wizard. Names with a middle name or a two-word surname will split into more columns than you expect, so check the preview first.
Yes. Tick Other in step 2 and type the character into the box beside it, such as a hyphen, pipe or semicolon. Only one character can go in that box.
Text to Columns remembers its last settings for the rest of the session and applies them to pasted text. Run the wizard once on any cell with all delimiters unticked to clear it.
Select the column, open Text to Columns, and click Finish straight away without changing anything. Excel re-evaluates each cell on the way out and converts text-formatted numbers into genuine numeric values.
Text to Columns is a one-off action that works in every version of Excel. Flash Fill guesses a pattern from an example you type. TEXTSPLIT is a formula that updates automatically when the source changes, but it needs Microsoft 365.
No. It only accepts a single column of source data. Select one column, run the wizard, then repeat for the next.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →