Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
TRIM / CLEAN
Text & cleanupStrip stray spaces and junk characters so lookups match and lists de-duplicate.
Difficulty
1What is it?
TRIM removes leading and trailing spaces and collapses runs of spaces between words down to one — the single most useful cleanup function, because invisible trailing spaces are the reason a lookup returns #N/A or a name shows up twice in a "unique" list. CLEAN is its partner: it strips non-printing characters (line breaks, control codes) that ride along when you paste out of a PDF, email, or web page. TRIM does NOT remove non-breaking spaces from web copies — for those, pair it with SUBSTITUTE.
2What it looks like
TRIM(text)- text
- The string to clean — usually a cell you pasted or imported.
3When you use it
- Fix a lookup that fails because a key has a hidden trailing space.
- Clean an imported customer or account column so duplicates actually collapse.
- Wrap TRIM(CLEAN(cell)) to strip both stray spaces and line breaks from a paste.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Collapse leading, trailing, and double spaces to one — the fix for lookups that mysteriously miss. The result is shown in quotes so you can see the edges.
=TRIM(B2)"Jane Doe"| A | B | C | |
|---|---|---|---|
| 1 | Field | Value | Result |
| 2 | Raw text | Jane Doe | "Jane Doe" |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Spaces remainThe text has non-breaking spaces (CHAR 160), common in web/PDF copies.Fix: TRIM(SUBSTITUTE(text, CHAR(160), " ")) to convert them first, then trim.
Line breaks remainA cell has embedded returns, not spaces.Fix: Wrap in CLEAN — TRIM(CLEAN(text)) — to strip non-printing characters too.
#VALUE!The reference isn't text.Fix: TRIM works on text; a stray error cell upstream will pass its error through.
6Better functions & alternatives
- CLEAN — Removes non-printing characters (line breaks, control codes) — pair with TRIM.
- SUBSTITUTE — Remove or convert a specific character, like a non-breaking space.
- TEXTSPLIT — When the real fix is to break the messy cell into clean pieces.
Want TRIM / CLEAN already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →