TRIM
Clean the spaces.
Strip the stray spaces off imported text
The same text with no leading or trailing spaces, and single spaces between words
Syntax
=TRIM(text)
| Argument | Required | What goes here |
|---|---|---|
text | Required | The cell holding the messy text, usually A2. |
How to use TRIM
TRIM removes every space before the first character and after the last one, and collapses any run of spaces inside the text down to one.
It matters because a padded name is not equal to a clean one. " Brightwell Paper " and "Brightwell Paper" look identical in a cell and behave as two different suppliers in a lookup, a COUNTIF, or a pivot.
TRIM only deals with the ordinary space character. A non-breaking space pasted from a web page or a PDF survives it, which is why a name that still will not match can look clean on screen.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=TRIM(B2)The two spaces at each end come off. | brightwell paper |
=LEN(B2)-LEN(TRIM(B2))How many characters TRIM would remove, which is how you find out whether padding is the reason a lookup is failing. | 4 |
=PROPER(TRIM(B2))Clean the spaces first, then fix the capitals. The usual pair on an import. | Brightwell Paper |
=TRIM(B7)=TRIM(B2)The same supplier typed two ways, one padded at the ends and one with a double space in the middle. TRIM makes them one value. Take either TRIM off and this comes back FALSE. | TRUE |
Notes
- TRIM does not touch a non-breaking space. If a value still will not match after trimming, that is usually why.
- Excel compares text without regard to capitals, so "brightwell paper" equals "Brightwell Paper". Spaces are the difference it does care about.