A surprising amount of spreadsheet work is not arithmetic at all —
it’s surgery on text. The export gives you SMITH, John - HR and
the report needs three columns. The product code UK-2024-0173
contains the year, if only you could get it out. Every generation
of Excel users has learned to do this; only the current one gets
to do it without pain.
The old way was LEFT, RIGHT, MID and FIND nested into
formulas that looked like modem noise:
=MID(A2, FIND(",", A2)+2, FIND(" - ", A2)-FIND(",", A2)-2)
That extracts the first name. Obviously. Modern Excel (365) replaced the whole genre with three functions that say what they mean.
TEXTBEFORE and TEXTAFTER
=TEXTBEFORE(A2, ",") → SMITH
=TEXTAFTER(A2, " - ") → HR
Read them aloud and they’re already documented: the text before the comma; the text after the dash. The delimiter can be any string, and the pieces compose — first name, in the new dialect:
=TEXTAFTER(TEXTBEFORE(A2, " - "), ", ")
Before the dash, after the comma. Same result as the modem noise, and a colleague can read it on the first pass.
Both take an optional instance number — TEXTBEFORE(A2, "/", 2)
takes everything before the second slash, and -1 counts from
the end: TEXTAFTER(A2, "/", -1) is “the bit after the last
slash”, which is how you get a filename out of a path.
TEXTSPLIT: all the pieces at once
When you want every part, stop slicing and split:
=TEXTSPLIT(A2, ", ")
One formula, and the pieces
spill across the
columns to the right — however many there are. Multiple delimiters
go in braces ({", ", " - "}), and a second argument splits down
rows instead. It’s Text to Columns reborn as a formula: living,
refreshable, and undoable by deleting one cell.
Going the other way, TEXTJOIN glues pieces back together with a
delimiter and — its quiet superpower — an ignore empty switch:
=TEXTJOIN(", ", TRUE, B2:F2)
Address lines with gaps become one clean string, no stray commas.
The two habits that make text work safe
Trim first. The classic failure isn’t the formula — it’s the
delimiter not being what you think.
A "SMITH ,John" with the space on the wrong side of the comma
defeats a tidy TEXTBEFORE(A2, ", "). Run the
cleaning routine
before surgery, and when a split misbehaves, suspect the string,
not the scalpel.
Numbers that come out of text are text. Extract 2024 from
UK-2024-0173 and it looks like a year, but it’s a text-number —
it won’t sum, and it
won’t sort
with real numbers. Wrap the extraction in VALUE() (or --)
the moment it leaves the string, and give
real dates the same respect.
Where the old guard still stands
LEFT, RIGHT and MID haven’t retired — they’re right when the
cut is positional rather than delimited: fixed-width exports, the
first two characters of a code, an ISBN’s check digit. And in older
workbooks (or non-365 Excel) you’ll still meet the FIND-based
nests; you now know how to read them, and how to replace them the
next time they break — the same courtesy owed to
inherited VLOOKUPs.
Text functions sit in stage three of the path because they’re conditions’ quiet partner: half the columns you’ll ever SUMIFS by were carved out of a messier column first.
Say the cut out loud — before this, after that, split on the other — and there’s now a function with exactly that name. Text finally works the way you’d explain it.