BeginnerText Functions
Strip stray spaces from item codes with TRIM
Clean codes that arrive padded with spaces so lookups stop failing on them.
TRIM
4 min10 points
TRIM
Removes the spaces before and after text, and collapses repeated spaces inside it.
Syntax
TRIM(text)
Text Functions.
Practise TRIM1 interactive exercise, free, no account needed
A worked example
=TRIM(" HSC-04182 ")
Returns HSC-04182
Removes the spaces before and after the code, which is why a lookup that failed on the raw text succeeds on this.
Where it goes wrong
TRIM only removes ordinary spaces. Non-breaking spaces pasted from web pages and PDFs survive it, so text that looks clean can still fail a match.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- Typing the clean code works for one row and has to be done again for every row of every file the supplier sends. The formula has to take the padded code in A2 and clean it.Strip stray spaces from item codes with TRIM
- LEFT counts characters from the left, and the first two characters here are spaces — so you keep the padding and lose the end of the code. You need something that removes spaces wherever they sit.Strip stray spaces from item codes with TRIM
Practise TRIM
Knowing what a function does and being able to write it from memory are different skills. These close the gap.