Excel like a finance pro.
Split one column into several
AltA, ETables & dataAnd the hidden second use: forcing text numbers to become real numbers.
Difficulty
1What it does
Text to Columns splits a combined field on a delimiter — the memo line that arrives as Acme Corp | INV-1042 | 2026-08-31. That's the obvious use. The one nobody knows is that running it on a single column of text-formatted numbers and pressing Finish immediately converts the whole column to real numbers, because the wizard re-parses every value on the way out. It's the fastest fix for a column that sums to zero. The trap in both cases is that it overwrites whatever is to the right without asking.
2The shortcut
- 1Insert enough blank columns to the right to hold the pieces — it will overwrite without warning.
- 2Select the column, press
Alt + A,E, choose **Delimited**, and tick the separator. - 3For the number fix: select the column,
Alt + A,E, then just pressFinish.
3Before → after
Press the shortcut to play it on a sample sheet.
Before — one field doing three jobs, and a column that won't add up
| A — Memo | B — Amount (text) | |
|---|---|---|
| 1 | Acme Corp | INV-1042 | 2026-08-31 | 4,200 |
| 2 | Globex | INV-1043 | 2026-09-02 | 9,800 |
| 3 | SUM → 0 |
Two problems in one sheet. The obvious fix is the split; the second button is the one nobody knows about.
4When you use it
- Split a reference field into customer, invoice and date.
- Convert a column of text numbers so it finally sums.
- Separate first and last names from a single field.
Want these moves already done for you? Wauvel's free tools download as branded, formula-driven Excel models — Tables, named ranges, and all.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.