Skip to content

Excel function · Text

Excel TRIM function

TRIM deletes every space at the start and end of a text and reduces runs of spaces between words to a single space. It is the first fix for codes that will not match in lists imported from other systems.

In Turkish Excel: KIRP

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

English Excel=TRIM(text)
Turkish Excel=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

ArgumentRequiredDescription
textRequiredThe 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.

B2fx=TRIM(A2)
Clean extra spaces in codes: example table
ABC
1Raw CodeClean CodeSpaces Removed
2 KBL-1525KBL-15251
3PRZ-0210 PRZ-02102
4SGT 0016SGT 00161
5KNL-2040KNL-20400
  • 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.

C2fx=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not found")
Find the code that will not match because of a space: example table
ABCDEFG
1Order CodeQtyPrice (raw)Price (TRIM)Product CodePrice
2KBL-1525 120Not found52.00KBL-152552.00
3PRZ-02102592.5092.50PRZ-021092.50
4 SGT-001610Not found132.00SGT-0016132.00
5KNL-2040 40Not found68.00KNL-204068.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.

B2fx=TRIM(A2)
TRIM versus removing every space: example table
ABC
1Code as TypedTRIMAll Spaces Removed
2KBL 1525KBL 1525KBL1525
3 PRZ 0210 PRZ 0210PRZ0210
4SGT 0016 SGT 0016SGT0016
5KNL 2040KNL 2040KNL2040
  • 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

Type this in Nexell

Clean the extra spaces in the order codes and bring in the unit price from the price list

Convert to table

Bring 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

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free