2.5 Text functions and cleaning up strings
You can split, join and repair messy text without retyping it.
Before:01. Excel — First StepsUnlocks:03. Excel — Analysis
Messy text — names in the wrong case, codes glued together, invisible spaces — gets repaired with functions instead of retyping, and this topic covers the classic set from LEFT and MID through TEXTSPLIT and TEXTJOIN. It sits in the formula module because text repair is usually the step before any lookup works. The confusion that costs the most time is the invisible one: two cells that look identical but differ by a trailing space or a non-breaking space, which is why TRIM and CLEAN come first.
Work through these
LEFT, RIGHT, MID, LEN, FIND, SEARCH
Taking pieces from the start, end or middle of a value, measuring its length, and finding something inside it. These are the building blocks that older text repairs are assembled from.
TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER
The newer text functions split, join, and take what falls before or after a marker, doing in one step what used to take several. They are considerably easier to read afterwards.
TRIM, CLEAN, PROPER, UPPER, LOWER and the invisible-space bug
Removing stray spaces, stripping unprintable characters and changing capitalisation, plus the invisible space that makes two identical-looking values refuse to match. That last bug wastes more time than any other in this module.
SUBSTITUTE and REPLACE
Replacing text by what it says, and replacing it by where it sits, are two different operations. Choosing the wrong one damages values that happened to contain the same characters.
Practice file
- xl-06-messy-data.xlsx
A deliberately horrible export: merged headers, blank rows, mixed date formats, stray text in number columns. The Power Query exercise.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.