What it does
Imported data is full of spaces you cannot see: one at the end of a code, two at the start of a name, an extra one between words. To Excel, "KBL-1525 " and "KBL-1525" are different texts; VLOOKUP will not find one from the other and COUNTIF will not count it. TRIM removes those spaces and returns a clean copy of the text.
TRIM only removes the ordinary space character (code 32). Text copied from web pages or some programs may contain non-breaking spaces (code 160), and TRIM leaves them alone. Turn them into ordinary spaces with SUBSTITUTE first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). For non-printing characters such as line breaks, use CLEAN.
To replace the source column with the cleaned one, copy the results and paste them with Paste Values. You can also use TRIM directly inside a lookup: =VLOOKUP(TRIM(A2),$E$2:$F$5,2,FALSE).
Syntax
=TRIM(text)=KIRP(metin)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text, or cell, to clean. Single spaces between words are kept; only the extra ones are removed. |
Examples
1Clean extra spaces in codes
Column B returns the clean codes, and column C shows how many spaces were removed in each row. A double space between parts becomes a single one.
=TRIM(A2)| A | B | C | |
|---|---|---|---|
| 1 | Raw Code | Clean Code | Spaces Removed |
| 2 | KBL-1525 | KBL-1525 | 1 |
| 3 | PRZ-0210 | PRZ-0210 | 2 |
| 4 | SGT 0016 | SGT 0016 | 1 |
| 5 | KNL-2040 | KNL-2040 | 0 |
- B2 (filled down to B5)
=TRIM(A2) - C2 (filled down to C5)
=LEN(A2)-LEN(B2)
The first three rows lose 1, 2 and 1 spaces; B4 becomes SGT 0016. The last row was already clean, so its difference is 0.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Find the code that will not match because of a space
Some order codes contain invisible spaces. Column C looks the code up as it is; column D cleans it with TRIM first.
=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not found")| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order Code | Qty | Price (raw) | Price (TRIM) | Product Code | Price | |
| 2 | KBL-1525 | 120 | Not found | 52.00 | KBL-1525 | 52.00 | |
| 3 | PRZ-0210 | 25 | 92.50 | 92.50 | PRZ-0210 | 92.50 | |
| 4 | SGT-0016 | 10 | Not found | 132.00 | SGT-0016 | 132.00 | |
| 5 | KNL-2040 | 40 | Not found | 68.00 | KNL-2040 | 68.00 |
- C2 (filled down to C5)
=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not found") - D2 (filled down to D5)
=VLOOKUP(TRIM(A2),$F$2:$G$5,2,FALSE)
The three codes with spaces are not found in column C (Not found); only the clean PRZ-0210 finds its price, 92.50. In column D all four codes match: 52.00, 92.50, 132.00, 68.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3TRIM versus removing every space
TRIM keeps one space between words. If a code must not contain any spaces, delete them all with SUBSTITUTE.
=TRIM(A2)| A | B | C | |
|---|---|---|---|
| 1 | Code as Typed | TRIM | All Spaces Removed |
| 2 | KBL 1525 | KBL 1525 | KBL1525 |
| 3 | PRZ 0210 | PRZ 0210 | PRZ0210 |
| 4 | SGT 0016 | SGT 0016 | SGT0016 |
| 5 | KNL 2040 | KNL 2040 | KNL2040 |
- B2 (filled down to B5)
=TRIM(A2) - C2 (filled down to C5)
=SUBSTITUTE(A2," ","")
On the second row TRIM returns PRZ 0210 and SUBSTITUTE returns PRZ0210. Which one you need depends on how the codes are written in the list you match against.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Spaces are still there
Cause: They are not ordinary spaces but non-breaking spaces from the web (
CHAR(160)), which TRIM does not recognise.Fix: Convert them first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")).- Still no match
Cause: TRIM always returns text. If the other list stores the code as a number, the text "1525" does not match the number 1525.
Fix: For numeric codes, convert the result:
=--TRIM(A2).
Good to know
- To see whether a column has extra spaces, use
=LEN(A2)-LEN(TRIM(A2)); rows above 0 contain extra spaces. - To remove every space, including those between words, use
=SUBSTITUTE(A2," ","")rather than TRIM.
In Nexell
Get Nexell to do it from a sentence
Clean the extra spaces in the order codes and bring in the unit price from the price list
Convert to tableBring the order list and the price list into the tool and type this. Nexell cleans the codes, matches the prices and shows in the preview how many rows matched; nothing changes until you approve.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
Why is TRIM not removing spaces?
Most likely they are non-breaking spaces (code 160). TRIM only removes ordinary spaces (code 32). =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) cleans both.
How do I remove all spaces between words?
TRIM leaves one space between words. To remove them all, use =SUBSTITUTE(A2," ",""); "KBL 1525 SY" becomes "KBL1525SY".
Related functions
- CLEANTEMİZ · Removes non-printable characters.
- SUBSTITUTEYERİNEKOY · Substitutes new text for old text.
- LENUZUNLUK · Number of characters.
- PROPERYAZIM.DÜZENİ · Capitalises the first letter of each word.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.