All versionsExamples need Excel 2021 or laterText

TRIM

SPATIES.WISSEN in Dutch Excel

Removes spaces at the start and end of a text and collapses repeated spaces between words to one.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1RelationName as typedClean nameLength
2D1001HoekstraInstallatie BVHoekstra Installatie BV=TRIM(B2)=TRIM(B2)
3D1002Bouwbedrijf Kok BV
4D1003Jansen Transport
5D1004MeijerGroothandel
6D1005Smit Horeca BV
Marks: extra spaceNote: B2 starts with two spaces, has two spaces between Hoekstra and Installatie, and ends with one space. B4 starts with one space; B5 has two spaces in the middle; B6 ends with two.

Result: Hoekstra Installatie BV

=TRIM(B2)
=TRIM(B2)

What TRIM does and when to use it

What it does: it removes the spaces at the start and end of a text and reduces repeated spaces between words to one, for example in a relation name typed by hand.

When to use it: on anything typed by hand or pasted from another system, before you compare, look up or count it. A trailing space is invisible and breaks exact matches, so TRIM fixes a whole class of #N/A errors in lookups. If LEN still shows an extra character, the cause is usually CHAR(160); SUBSTITUTE removes that, and CLEAN handles control characters.

How Excel evaluates it

Excel reads the text and walks through it from the first character:

  • spaces before the first non-space character are dropped
  • between words, the first space of a run is kept and any further spaces in that run are dropped
  • spaces after the last non-space character are dropped
  • every character that is not a space with code 32 is copied as it is, which is why a tab or a non-breaking space survives

The result is always text, and it is a new value: the source cell is not changed.

Watch out: TRIM removes only the ordinary space character. Anything else, including the non-breaking space that web pages and Word documents leave behind, passes through untouched.

Mental model

A proofreader who closes up every gap wider than one space and cuts the blank margins, but only recognizes the gaps made with the space bar.

Terms used on this page: cell

Syntax

Official TRIM documentation
TRIM(text)
TRIM(text)
ArgumentTypeMeaning
textRequiredThe text to clean; a cell reference or text in quotes.

Return value:Text: the text without extra spaces

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Clean relation names in a debtors list

The client's debtors list was typed by hand in the administration. Three things went wrong in the names:

  • some have a space before them
  • some have two spaces in the middle
  • one has spaces at the end

When you match the list to the receivables ledger, sorting and filtering behave oddly because of it. You add a column with the cleaned names.

ABCDE
1RelationName as typedClean nameLength
2D1001HoekstraInstallatie BVHoekstra Installatie BV=TRIM(B2)=TRIM(B2)
3D1002Bouwbedrijf Kok BV
4D1003Jansen Transport
5D1004MeijerGroothandel
6D1005Smit Horeca BV
7
Formula cell (C2)InputMarks: extra spaceNote: B2 starts with two spaces, has two spaces between Hoekstra and Installatie, and ends with one space. B4 starts with one space; B5 has two spaces in the middle; B6 ends with two.

Formula in C2

=TRIM(B2)
=TRIM(B2)

Result: Hoekstra Installatie BV

Step by step
  1. Excel reads B2. On screen it looks like Hoekstra Installatie BV, but the cell holds 27 characters: two spaces, Hoekstra, two spaces, Installatie, one space, BV, one space (2 + 8 + 2 + 11 + 1 + 2 + 1 = 27).
  2. The two spaces before Hoekstra come before any other character, so both are dropped.
  3. Between Hoekstra and Installatie there are two spaces. The first is kept, the second is dropped: Hoekstra Installatie.
  4. Between Installatie and BV there is one space, which is kept. The space after BV comes after the last real character and is dropped.
  5. C2 shows Hoekstra Installatie BV, 23 characters (8 + 1 + 11 + 1 + 2). Put =LEN(B2) in D2 to see 27 and =LEN(C2) to see 23; the 4 missing characters are the spaces that were removed.
  6. Copy C2 down. Bouwbedrijf Kok BV was already clean and comes back identical; the other three lose their extra spaces the same way.

Watch out: If a name came from a web portal and LEN(C2) is still one higher than you expect, that last character is probably a non-breaking space; the Advanced example deals with it.

More on this example: why it works, 1 variation

Why it works

TRIM applies three rules at once, edges and runs, and each of them only ever removes spaces, so the words themselves cannot be damaged. Because you cannot see spaces in a cell, LEN is the way to prove they were there and are now gone.

Variations

=TRIM(B2:B6)
=TRIM(B2:B6)

In Excel 2021 or Microsoft 365 one formula in C2 spills all five cleaned names.

IntermediateExcel 2021 or later

Match an invoice to the aging list despite a trailing space

You reconcile the receivables ledger to the debtors aging list from the client. When you look up invoice VF-2025-1044 the formula returns #N/A, even though the invoice is visibly in the list. The number was typed with a space after it.

You wrap the key column in TRIM inside the lookup, so the client's list can stay as it is.

ABCDEFG
1Invoice no.RelationOpen amountInvoice no.Open amount
2VF-2025-1041D1001€12,100.00€ 12.100,00VF-2025-1044€8,470.50€ 8.470,50=XLOOKUP(E2, TRIM(A2:A6), C2:C6)=XLOOKUP(E2; TRIM(A2:A6); C2:C6)
3VF-2025-1042D1004€3,025.50€ 3.025,50
4VF-2025-1043D1002€18,150.00€ 18.150,00
5VF-2025-1044D1003€8,470.50€ 8.470,50
6VF-2025-1045D1005€960.40€ 960,40
7
Formula cell (F2)InputMarks: extra spaceNote: A5 holds VF-2025-1044 followed by one space, 13 characters instead of 12.

Formula in F2

=XLOOKUP(E2, TRIM(A2:A6), C2:C6)
=XLOOKUP(E2; TRIM(A2:A6); C2:C6)

Result: €8,470.50€ 8.470,50

Step by step
  1. Excel evaluates TRIM(A2:A6) first. It cleans each of the five invoice numbers: four are unchanged, and the fourth, VF-2025-1044 with a trailing space, becomes VF-2025-1044. The result is a five-item array of clean numbers.
  2. XLOOKUP looks for E2, VF-2025-1044, in that array: VF-2025-1041, no; VF-2025-1042, no; VF-2025-1043, no; VF-2025-1044, yes. The match is at position 4.
  3. It returns the fourth cell of C2:C6, which is C5: 8,470.508.470,50.
  4. Without TRIM, the fourth value in lookup_array is VF-2025-1044 followed by a space, 13 characters. Exact match compares all 13 against the 12 in E2, finds a difference, and returns #N/A. That is what you saw before.
  5. Type VF-2025-1043 in E2 and F2 shows 18,150.0018.150,00: the cleaned array serves every lookup, not only the broken one.

Watch out: If the space is in E2 instead of the list, TRIM on the list does not help. Wrap E2 too: XLOOKUP(TRIM(E2), TRIM(A2:A6), C2:C6).

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

XLOOKUP accepts an array where it expects a range, so TRIM can clean the key column on the fly without a helper column. The lookup then compares clean against clean.

In Excel 2019 or older, XLOOKUP is not available and INDEX/MATCH needs Ctrl+Shift+Enter for the same trick; there, a helper column with TRIM is the practical route.

More to watch out for

  • TRIM on a key column of 400,000400.000 journal lines runs on every recalculation. Fine here; on a full auditfile export, clean the column once and paste as values.

Variations

=XLOOKUP(TRIM(E2), TRIM(A2:A6), C2:C6, "Not in aging list")
=XLOOKUP(TRIM(E2); TRIM(A2:A6); C2:C6; "Not in aging list")

Both sides cleaned and a readable message for invoices that really are not in the aging list, which is itself an audit finding.

Advanced

Find the bank counterparties where TRIM is not enough

You copied the counterparty names from the client's online banking portal into the bank statement sheet, to match them with the relation names in the ledger. After TRIM, two names still fail to match.

You suspect non-breaking spaces, CHAR(160), which look like spaces and survive TRIM. You want a flag per row that says whether the cleaned name still contains one, so you know which rows need SUBSTITUTE.

ABCDE
1CounterpartyEntry no.Check
2Hoekstra Installatie BV25-0412OK=IF(ISNUMBER(FIND(CHAR(160), A2)), "Non-breaking space", "OK")=IF(ISNUMBER(FIND(CHAR(160); A2)); "Non-breaking space"; "OK")
3De BoerSchilderwerk25-0413
4Kok Bouw BV25-0414
5Meijer Groothandel25-0415
6Smit Horeca25-0416
7
Formula cell (D2)InputMarks: extra space, non-breaking spaceNote: A2 ends with an ordinary space and A6 starts with one. A3 has a non-breaking space (code 160) between De Boer and Schilderwerk instead of a normal space, and A5 ends with one. On screen all four look like ordinary spaces.

Formula in D2

=IF(ISNUMBER(FIND(CHAR(160), A2)), "Non-breaking space", "OK")
=IF(ISNUMBER(FIND(CHAR(160); A2)); "Non-breaking space"; "OK")

Result: OK

Step by step
  1. Excel searches A2 for the character with code 160. A2 is Hoekstra Installatie BV with an ordinary space (code 32) at the end, so there is no CHAR(160) in it and FIND returns #VALUE!. ISNUMBER turns that into FALSE.
  2. With FALSE, IF returns the second text: OK. D2 shows OK. The trailing ordinary space is not a problem for this test, because TRIM removes it anyway.
  3. Copy D2 to D3. A3 is De Boer, CHAR(160), Schilderwerk. De Boer is 7 characters, so the CHAR(160) is character 8 and FIND returns 8. ISNUMBER(8) is TRUE and D3 shows Non-breaking space, even though the non-breaking space sits between two words.
  4. D4 (Kok Bouw BV) has no CHAR(160) and shows OK. D5 is Meijer Groothandel followed by a CHAR(160): the name has 18 characters, so FIND returns 19 and D5 shows Non-breaking space. D6 starts with an ordinary space, so it shows OK.
  5. The test looks for the character itself, so it does not matter where it stands: at an edge, between words or next to another space. A3 and A5 are the rows that need the SUBSTITUTE fix in the second variation before you match them to the ledger.

Watch out: A shorter-looking test compares lengths: LEN(TRIM(A2)) <> LEN(TRIM(SUBSTITUTE(A2, CHAR(160), " "))). It misses A3.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

FIND looks for exactly the character you give it, here the one with code 160, and returns its position or #VALUE! when it is absent. ISNUMBER turns that into TRUE or FALSE, so IF can choose the text. The test checks the character itself, not what TRIM would do with it, which is why it also catches a non-breaking space between two words.

Watch out for

  • A shorter-looking test compares lengths: LEN(TRIM(A2)) <> LEN(TRIM(SUBSTITUTE(A2, CHAR(160), " "))). It misses A3.

    There the CHAR(160) sits between two words, and TRIM removes nothing on either side: both lengths are 20, so 20 <> 20 is FALSE and the row shows OK although the match with the ledger will fail. It only flags a CHAR(160) at an edge or next to another space, as in A5 (19 against 18).

  • CHAR(160) is the common culprit, but not the only one: text from PDFs can hold CHAR(9) tabs, which need CLEAN, and other Unicode spaces that need UNICHAR.

Variations

=IF(LEN(TRIM(A2)) <> LEN(TRIM(SUBSTITUTE(A2, CHAR(160), " "))), "Non-breaking space", "OK")
=IF(LEN(TRIM(A2)) <> LEN(TRIM(SUBSTITUTE(A2; CHAR(160); " "))); "Non-breaking space"; "OK")

Compares the length after TRIM with and without the SUBSTITUTE step. Do not use it as the check: D3 shows OK (20 against 20) and D5 shows Non-breaking space (19 against 18), so it misses the non-breaking space between two words.

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
=TRIM(SUBSTITUTE(A2; CHAR(160); " "))

The fix rather than the test: replace every non-breaking space with a normal one, then TRIM.

MasterExcel 2021 or later

A full cleaning step for imported entry numbers, in LET

Every week the client sends a fresh auditfile export, and you match its entry numbers (boekstuknummers) to the client's list of manual journal entries. The numbers in the export carry a mix of:

  • trailing spaces
  • non-breaking spaces
  • tabs
  • the occasional line break

Each week someone spends an hour finding out why the lookups fail. You write one cleaning formula that handles all of it in a fixed order, then reuse it for the key column and the lookup value alike.

ABCDEF
1Entry no. from exportDebitClean entry no.Length
225-0412€1,250.00€ 1.250,00
325-0413€8,400.00€ 8.400,00
425-0414€560.00€ 560,00
525-0415 €3,025.00€ 3.025,0025-0415=LET( raw, A5, noControl, CLEAN(raw), noNbsp, SUBSTITUTE(noControl, CHAR(160), " "), TRIM(noNbsp) )=LET( raw; A5; noControl; CLEAN(raw); noNbsp; SUBSTITUTE(noControl; CHAR(160); " "); TRIM(noNbsp) )
625-0416€4,200.00€ 4.200,00
7
Formula cell (D5)InputMarks: non-breaking space, tab, line feedNote: A2 ends with a tab (code 9). A3 starts with a non-breaking space (code 160). A4 ends with a line break (code 10). A5 ends with a normal space followed by a non-breaking space. A6 is clean. On screen the five look almost the same: only A3 is visibly indented, and the other differences cannot be seen.

Formula in D5

=LET(
  raw, A5,
  noControl, CLEAN(raw),
  noNbsp, SUBSTITUTE(noControl, CHAR(160), " "),
  TRIM(noNbsp)
)
=LET(
  raw; A5;
  noControl; CLEAN(raw);
  noNbsp; SUBSTITUTE(noControl; CHAR(160); " ");
  TRIM(noNbsp)
)

Result: 25-0415

Step by step
  1. raw is A5: 25-0415, a normal space, a non-breaking space. That is 7 + 1 + 1 = 9 characters; =LEN(A5) in E5 confirms 9.
  2. noControl is CLEAN(raw). CLEAN removes characters with codes 0 to 31. Neither the space (32) nor the non-breaking space (160) is in that range, so nothing changes here: still 9 characters. For A2 and A4 this is the step that removes the tab and the line break.
  3. noNbsp is SUBSTITUTE(noControl, CHAR(160), " "): the non-breaking space at the end becomes a normal space. The text is now 25-0415 followed by two normal spaces, 9 characters.
  4. TRIM(noNbsp) removes both trailing spaces. D5 shows 25-0415, 7 characters, and it now matches an exact lookup.
  5. Copied to D2:D6 the formula gives 25-0412 through 25-0416, every one 7 characters: CLEAN took the tab from A2 and the line break from A4, SUBSTITUTE and TRIM took the non-breaking space from A3, and A6 passed through unchanged.
  6. Swap the order and put TRIM first: for A5, TRIM sees the normal space sitting between the 5 and the non-breaking space, treats it as a space between words, and keeps it. SUBSTITUTE then turns the non-breaking space into a normal one, and nothing removes the two trailing spaces that are left. The result would still be 9 characters and the lookup would still fail. TRIM must be last.

Watch out: The mirror image, TRIM first and SUBSTITUTE after, is the version most people write, and it leaves a space on every entry number that ended in a non-breaking space, because the SUBSTITUTE creates a normal space after TRIM has already run. The numbers look clean and the lookups still fail.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Each function handles one class of character, and the order is chosen so that every earlier step turns its class into something a later step can remove: CLEAN deletes control codes outright, SUBSTITUTE converts code 160 into code 32, and TRIM, last, removes all the code-32 spaces that the previous steps left or created. LET names the stages so the next person reads the pipeline top to bottom.

Do not use this construction for a one-time import: run Find and Replace on the column (Ctrl+H, with Alt+0160 on the numeric keypad as the search text) and be done.

The alternative for a weekly file is Power Query, whose Trim and Clean steps remember the recipe, at the cost of a manual refresh; note that its Trim only removes leading and trailing spaces and leaves repeated spaces between words.

More to watch out for

  • CLEAN removes a line break by deleting it, not by turning it into a space. Two words separated by a line break become one word. For free text such as journal descriptions, SUBSTITUTE CHAR(10) with a space first; for entry numbers it does not matter.
  • Zero-width and other Unicode spaces (codes above 255) survive all three functions. If LEN still disagrees after this, use UNICODE on each character to find the code, then SUBSTITUTE with UNICHAR.

Variations

=TRIM(SUBSTITUTE(CLEAN(A5), CHAR(160), " "))
=TRIM(SUBSTITUTE(CLEAN(A5); CHAR(160); " "))

The same pipeline without LET, for a quick helper column. Read it inside out.

=TRIM(SUBSTITUTE(CLEAN(A2:A6), CHAR(160), " "))
=TRIM(SUBSTITUTE(CLEAN(A2:A6); CHAR(160); " "))

In Excel 2021 or Microsoft 365, one formula in D2 cleans the whole column and spills; use the same expression around a lookup_array.

Common mistakes

SymptomCauseFix
#N/A although TRIM is on the lookup valueYou wrapped the lookup value in TRIM and the XLOOKUP still returns #N/A.Check the other side: the key column in the table may hold the space. Either TRIM the whole column in a helper column, or use TRIM(A2:A50) as lookup_array in Excel 2021 or Microsoft 365. If LEN still disagrees after that, look for CHAR(160).
The total of the cleaned amounts is 0You applied TRIM to a column of amounts and the total below it became 0.TRIM returns text, and SUM ignores text. Wrap it in VALUE if the amounts were text with spaces, or leave numeric columns alone.
A line break is still in the textYou expected TRIM to remove the line break inside an address pasted from a website, and it is still there.A line break is CHAR(10), not a space. CLEAN removes it; TRIM only handles code 32.

Used in combinations

  • Split a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later