Text to Columns in Excel: Split One Column Into Many

Kirkgate Veterinary Supplies distributes pharmaceuticals, consumables and cold-chain stock to 340 practices across Yorkshire and the North East from a single warehouse in Otley. The monthly supplier payment run is 1,148 lines and £1,906,400, built on a sheet called PayRun and uploaded to the bank as a Bacs file on the last working day of the month.

For six years the accounts system exported bank details as two fields: Sort code and Account, both stored as text, both correct. On 24 August 2026 it was upgraded, and the new export writes one field — Bank, in column H, in the form 04-00-72/00318842.

So on 31 August somebody did the obvious thing. Selected column H. Data → Data Tools → Text to Columns. Delimited. Ticked Other and typed -, ticked the / box under Other as well by typing over it, took one look at step 3, and pressed Finish.

Step 3 said General. It says General every time. General is the default and it is the first of the four radio buttons, and what it means is: read each piece, and if it looks like a number make it a number.

04 looks like a number. It became 4.

00 became 0. 00318842 became 318842. Forty seconds of work, no warning, no error, no red.

• 613 of the 1,148 lines had a leading zero somewhere in the sort code or the account number. Sort codes came out as 4-0-72. Eight-digit accounts came out with six or seven digits.
• The Bacs file went up at 16:20. The bank's validation rejected 613 lines worth £994,100 and paid the other 535.
• Nobody was in on the Saturday. The rejections were read on Tuesday 1 September, and the re-run cleared on 9 September — nine days late.
• 47 suppliers were paid by same-day CHAPS to stop the bleeding, at £25 each: £1,175 in fees.
• Ferndale Cold Logistics, who run Kirkgate's refrigerated collections, suspended the account on 8 September. Two days of vaccine deliveries sat out of temperature. £18,700 of stock was destroyed, and the destruction had to be reported.
• One supplier invoiced £2,400 of statutory interest under the Late Payment of Commercial Debts Act and was entitled to every penny of it.

That is £22,275 and nine days, and it is not the part of this story that matters.

Here is the part that matters. Splitting 04-00-72/00318842 needs four columns. Column H was one column. Excel asked — once, in a small box, in the most general words it has:

There's already data here. Do you want to replace it?

Somebody pressed OK, because of course there is already data there, this is a spreadsheet.

Columns I, J and K held Payment ref, Approved by and Approval date: the internal authorisation trail, filled in line by line by two managers over the previous week. They sat to the right of the frozen pane at column H, so nobody had scrolled past them in days. The split wrote over all three.

Undo would have brought them back. The file was saved and closed at 17:40 and the workbook was opened fresh on Tuesday, so there was no undo left to press, and the most recent backup on the server was Monday morning's copy — before the approvals were entered.

It was found on 22 September, three weeks later, when the auditors pulled a sample of forty payments and asked who had approved them. The answer was that nobody could say. 1,148 payments were re-approved by hand from email and purchase orders over four days.

Text to Columns did nothing wrong. It is not a formula and it is not a query. It is an edit — it changes the cells you point it at, it writes over the cells next to them, and when it is finished there is nothing anywhere in the file that records that it ran, what it was set to, or what used to be there.

1) The Column as the Wizard Read It

Three things in that table are worth saying out loud.

A leading zero is a digit, not formatting. 04 and 4 are the same number and different sort codes. The moment General decides a piece of text is a number, the zero is gone — not hidden, not formatted away, gone, because the cell now holds the value 4 and 4 has one digit.

It is per-piece, not per-row. Row 4 of the example (60-16-13/41288607) came through perfectly, because none of its pieces started with a zero. 535 lines were fine. That is exactly what makes this expensive: a wholly broken file gets rejected wholesale and fixed on the spot, and a 53%-broken file gets half-paid and argued about for a fortnight.

Nothing on screen distinguishes the two. 4 in a cell that should say 04 is left-aligned, right-aligned, black, in the same font, under the same header.

🎯 Scenario: Before you split anything, put =SUMPRODUCT(--(LEFT(H2:H1149,1)="0")) in a spare cell. That is how many rows have something to lose. If it is not zero, General is not a choice you are allowed to make.

2) Delimited or Fixed Width, and What Each One Cannot See

Step 1 asks one question and the answer is usually obvious, but the two branches fail in opposite directions.

Delimited cuts at characters: Tab, Semicolon, Comma, Space, or anything you type into Other. Two things about it are not obvious:

• You can tick several at once, and Other holds only one character — so 04-00-72/00318842 needs - in Other plus a second pass, or one of the standard boxes doing the other job. People solve this by running the wizard twice, which is where step-3 settings from run one quietly govern run two.
• Treat consecutive delimiters as one is unticked by default. On a fixed-width report pasted in as text, where fields are separated by runs of spaces, leaving it unticked produces a column per space and a row that is mostly empty cells.

Fixed width cuts at positions, and positions are a property of the file you are looking at, not of the data. The preview pane shows break lines you can drag; they are set from the rows visible in that preview. A description field that is longer on row 900 than on any of the first twenty rows will be cut in the wrong place, and the preview will never show you that row.

Text qualifier (step 2, on the Delimited branch) is the " that says the commas inside these quotes are data. Leave it on ". Set it to {none} and a single quoted address field with a comma in it turns one row into two columns' worth of nonsense.

🎯 Scenario: On the Fixed width branch, before you click Finish, press Ctrl+End to find the last row and check its length: =MAX(LEN(H2:H1149)) against the position of your last break. If the longest value is longer than the last break, you are about to truncate the rows you have not looked at.

3) Step 3 Is the Whole Article

Steps 1 and 2 decide where the cuts go, and you can see the cuts in the preview. Step 3 decides what each resulting column is, and you cannot see that anywhere — which is why it is the step everybody clicks past.

There are four options, and you set them per column, by clicking the column's heading in the preview first:

General is selected for every column, every time the wizard opens. It is not remembering a choice; it is the default, and it is the right default for perhaps a third of real splits.

The rule that keeps you out of trouble is short: if a column is an identifier rather than a quantity, it is Text. Account numbers, sort codes, part numbers, invoice references, postcodes, NI numbers, batch codes, barcodes. You are never going to add two of them together. Nothing is lost by storing them as text and everything is lost by not.

🎯 Scenario: Open the wizard on a column you split last month and get to step 3 without clicking Finish. Click each column heading in the preview in turn and read the radio button. Then press Cancel. If more than one identifier column says General, you already have this problem and have not been told about it yet.

4) General Strips Leading Zeros, and No Format Puts Them Back

This is worth being precise about, because the usual fix is not a fix.

When General turns 00318842 into the number 318842, the cell contains the number 318842. You can now apply a custom number format of 00000000 and the cell will display 00318842 again, and it will look completely repaired. It is not repaired. LEN still says 6, =A2="00318842" is still FALSE, a CSV export still writes 318842, and the Bacs file still gets rejected — because a number format changes what is drawn on the screen and never what is in the cell.

There are exactly two honest positions:

• Prevent it: set the column to Text in step 3 before you press Finish.
• Repair it, if and only if the width is genuinely fixed: =TEXT(A2,"00000000") for an eight-digit account, =RIGHT("00"&A2,2) for a two-digit sort-code pair. Both produce real text with real zeros.

A width you are guessing at is not a repair. If the original file is still on disk, re-split it with Text; that is faster than arguing about whether every account is eight digits.

🎯 Scenario: =SUMPRODUCT(--(LEN(L2:L1149)<>8)) over the account column. Every UK account number is eight digits, so this must be zero. Put the same check on the sort-code columns with <>2. Three cells, and they catch the entire class of fault before the file leaves the building.

5) General Also Reads Codes as Dates, and Long Numbers as Science

Leading zeros are the common case. These are the ones that are harder to spot, because the result looks deliberate.

The date cases are the nastiest, because a date in a code column is still a value, still right-aligned, still sortable. MAR-26 as 01/03/2026 will sort, filter, group and export without complaint. It only stops looking fine when somebody tries to match it against the supplier's batch list and 40% of it does not match.

The barcode case is worse in a specific way: beyond 15 significant digits Excel does not round the display, it discards the digits. That is not a Text to Columns bug, it is the 15-digit precision limit, and General is simply the thing that walked your barcode into it.

🎯 Scenario: Put =COUNT(J2:J1149) on any column that should be entirely codes. COUNT counts numbers and dates and ignores text, so on a column of identifiers the answer must be 0. Compare it with =COUNTA(J2:J1149) for the total. Any gap is a conversion nobody asked for.

6) Text, Date and Skip: the Three Buttons That Fix It

Text is the one you will use most, and the discipline is to click it for each identifier column, not once for the selection. The wizard sets the format for the highlighted preview column only. Click the heading, click Text, click the next heading, click Text.

Date, with its DMY / MDY / YMD dropdown, is the underrated one. When an export gives you 03/04/2026 and it means the 3rd of April, a UK sheet reads it correctly and a US sheet reads it as 4 March — and worse, a mixed file gets some rows converted and some left as text, because 13/04/2026 cannot be a US date and stays text while 03/04/2026 can and does. Setting the column to Date and choosing DMY tells Excel the order explicitly, and it is the only place in Excel where you get to state it rather than have it guessed. This is the proper fix for the problem covered in regional settings and date order, and it works whether or not you are splitting anything.

Do not import column (skip) discards a piece. Its real use is defensive: if a split would produce four columns and you only want two, skip the other two and Text to Columns writes two columns instead of four — which means it writes over two fewer columns to the right. Skip is the cheapest way to make the wizard less destructive.

🎯 Scenario: Take a column of dates that came in as text and are sitting left-aligned. Select it, run Text to Columns, Delimited, untick every delimiter, and on step 3 choose Date: DMY. Finish. The whole column converts in place, in the order you specified, with nothing split and nothing overwritten. Then check with =COUNT(range) — it should now equal =COUNTA(range).

7) The Advanced Button, for Numbers That Came From Somewhere Else

Step 3 has an Advanced button that most people have never pressed. It holds three settings, and they apply to the columns you have left on General:

• Decimal separator and Thousands separator. A German or Spanish export writes 1.234,56. Your UK sheet reads that as text, or as 1.23456, or as something worse. Set the decimal separator to , and the thousands separator to . in Advanced, and the wizard converts it correctly on the way in — without you touching the machine's regional settings, and without a nest of SUBSTITUTE calls.
• Trailing minus for negative numbers. Mainframe and SAP extracts write 1250- rather than -1250. Tick this and they come in as negatives. Leave it unticked and they come in as text, silently drop out of every SUM, and your total is quietly too high by twice the value of the credits.

Advanced is per-run and it is remembered, which cuts both ways: set it for a German file and the next person to split an English file inherits it.

🎯 Scenario: After importing any numeric column from a foreign-format file, compare =COUNT(range) with =COUNTA(range). If numbers are landing as text, the two disagree, and Advanced is where you fix it — before anybody builds a SUMIFS on top of a column that is half text.

8) It Writes Over Whatever Is to the Right

This is the fault that cost Kirkgate four days of re-approval, and it has a one-line fix that almost nobody uses.

Text to Columns writes its output starting at the column you split and running rightwards, one column per piece. It does not insert. It does not shift anything along. It overwrites, and the only warning is There's already data here. Do you want to replace it? — a sentence that does not say how many columns, which columns, or what is in them.

Three defences, in order of how much they are worth:

1. Use the Destination box. Step 3 has a Destination field, and it defaults to the cell you started from. Point it at the first empty column to the right of your data — $S$2 — and the split lands in clear space. The original column is untouched, which also means you keep a copy to check against. This is one click and it removes the entire failure mode.
2. Insert the columns first. Count the pieces, select that many column headers to the right, right-click, Insert. Now there is nothing to overwrite.
3. Skip what you do not need (section 6), so there are fewer columns to write.

And know what Undo will and will not do for you. Ctrl+Z does reverse a Text to Columns, overwrite included. It stops being available the moment the workbook is closed — and a payment run is exactly the kind of file that gets saved and closed at the end of the day and opened fresh the next morning.

🎯 Scenario: Before any split, =COUNTA(I1:K1149) across the columns immediately to the right. Write the number down. Run the split. Run the check again. If the number fell, something that was there is not there any more, and you have until the file closes to press Ctrl+Z.

9) It Is an Edit, Not a Formula — Nothing Records That It Ran

A TEXTSPLIT in a cell is visible forever. You can click the cell and read what it does. It re-runs when the source changes. Somebody reviewing the workbook in a year can see how the column was made.

A Text to Columns leaves nothing. Not a note, not a formula, not a setting, not a date. Next month's file arrives in the same shape and a different person has to work out what was done to it, from the shape of the output, and they will get it slightly different — a different delimiter set, General instead of Text, four columns instead of two.

So the honest comparison is not "wizard versus formula", it is "one-off versus repeatable":

For the Kirkgate case the formula is not even long. With the Bank column left alone in H:

I2: =TEXTBEFORE(H2,"/")
J2: =TEXTAFTER(H2,"/")

Two columns, live, self-documenting, and both results are text because TEXTBEFORE and TEXTAFTER return text — so the leading zeros are still there, without anybody having to remember a radio button. Split the sort code further with =TEXTSPLIT(I2,"-") if you need the three pairs separately.

If the file arrives every month, the answer is Power Query: Split Column by Delimiter, set the resulting columns to Text once, and every future file is one Refresh.

🎯 Scenario: Look at the last three columns somebody handed you as "already split". Click a cell in each. If the formula bar shows a value rather than a formula, you cannot tell how it was made or whether it was made the same way last month — and neither can the person who made it.

10) The One Job It Does Better Than Anything Else

None of the above means stop using it. Text to Columns is the fastest tool in Excel for a job that has nothing to do with splitting: converting a column in place.

Select the column, Text to Columns, Delimited, untick every delimiter, choose a format on step 3, Finish. Nothing is split. Every cell is re-read as the type you chose, in the cells they are already in.

That gives you three genuinely useful moves no formula does as cleanly:

• Numbers stored as text → real numbers. The column of left-aligned figures with the little green triangles, that SUM returns 0 for. Untick everything, leave it on General, Finish. Done, in place, no helper column, no paste-special.
• Text dates → real dates, in the order you specify. Untick everything, step 3, Date: DMY. This handles the mixed files where half the rows converted on import and half did not, which is the case DATEVALUE struggles with.
• Numbers → text, deliberately. Untick everything, step 3, Text. Turns a column of part numbers into text so nothing downstream reinterprets them.

There is one caveat worth knowing: on the first of those, leading zeros go the same way they went in Kirkgate's payment run. Converting text to numbers is exactly the operation that discards them. Which is the whole point of the article, stated the other way round — the wizard's conversion is not a bug, it is the feature, and the only question is whether you meant it this time.

🎯 Scenario: Find a column your SUM ignores. =COUNT(range) is 0 and =COUNTA(range) is 400. Run the untick-everything conversion and watch COUNT become 400. Then confirm no identifier column was in the selection.

11) Seven One-Cell Checks

All of these go in a spare cell above the data. None takes longer to write than the split takes to run.

1. What am I about to lose? =SUMPRODUCT(--(LEFT(H2:H1149,1)="0")) — rows whose value starts with a zero. Not zero means General is off the table.
2. What am I about to sit on top of? =COUNTA(I1:K1149) across the columns to the right, before and after. It must not fall.
3. Are the widths right? =SUMPRODUCT(--(LEN(L2:L1149)<>8)) for eight-digit accounts, <>2 for sort-code pairs, <>6 for postcode-ish codes. Must be 0.
4. Did anything become a number? =COUNT(J2:J1149) on a column of identifiers. Must be 0.
5. Did anything become a date? =SUMPRODUCT(--ISNUMBER(J2:J1149)) catches dates as well as numbers, since a date is a number. Must be 0.
6. Did the count survive? =COUNTA(H2:H1149) before against =COUNTA(L2:L1149) after. A file with a stray delimiter in one row produces a column count you did not expect and a value that landed one column over.
7. The round trip, which is the only check that proves it. Keep the original column (use the Destination box, section 8) and rebuild it from the pieces:

=SUMPRODUCT(--(I2:I1149&"-"&J2:J1149&"-"&K2:K1149&"/"&L2:L1149<>H2:H1149))

Zero means every one of the 1,148 rows rebuilds character for character into what it started as. Anything else is the exact count of rows that changed meaning, and you can filter to find them. Had that cell existed on 31 August it would have said 613.

🎯 Scenario: Put checks 1, 2 and 7 into the sheet you use for the next import, and colour them. Three cells. Then run your split and read them before you upload anything to anybody's bank.

12) Twelve Traps

1. General strips leading zeros, and a number format does not put them back. 00318842 becomes 318842; LEN is the test, not what you can see.
2. Step 3 is per column, not per split. Click each column heading in the preview and set its format, or every column but the last one you clicked stays on General.
3. Identifiers are Text. Sort codes, accounts, part numbers, postcodes, barcodes, NI numbers. If you will never add two of them together, they are text.
4. General reads codes as dates. MAR-26, 3-10 and 1/2 become date serials, and a date in a code column looks deliberate.
5. Beyond 15 digits, the extra digits are discarded, not rounded. A 19-digit barcode through General is unrecoverable.
6. It overwrites the columns to the right. The warning does not say how many or what. Use the Destination box, or insert columns first.
7. Undo works until the file closes. After that the columns you overwrote are as gone as the backup policy allows.
8. Other holds one character. Two different separators mean two passes — and pass two opens with pass one's settings.
9. Treat consecutive delimiters as one is unticked by default, so runs of spaces become empty columns.
10. Fixed width break lines are set from the rows in the preview, not from the longest row in the file. Check MAX(LEN()) first.
11. Advanced holds the decimal and thousands separators and the trailing-minus tick, it is remembered between runs, and 1250- without it is text that silently leaves your totals too high.
12. It leaves no record at all. No formula, no step list, no date, no settings. If the file comes back next month, use TEXTSPLIT or Power Query instead.

Kirkgate did not stop using Text to Columns. It is still the fastest way to fix a column of text-that-should-be-numbers, and it still gets used most weeks.

What changed on PayRun is smaller than you would expect. The Bank column is not split any more — two TEXTBEFORE/TEXTAFTER formulas in two clean columns to the right of the data do it, they run themselves, and they return text. And one cell sits above the header row holding check 7, the round trip, which rebuilds all 1,148 bank strings from the pieces and compares them with the originals. It says 0. When it does not say 0, it says how many.

The general lesson is not about a wizard. It is that General is not "leave it alone", it is "interpret it" — and the two are opposites. Excel offers to read your data for you at the exact moment it knows least about it: before it is in the sheet, before it has a header, before anybody has looked. Most of the time its reading is right, which is what makes the third of the time it is wrong so hard to notice, and what makes 04 come out as 4 at twenty past four on the last working day of the month with the Bacs file already going up.