TRIM removes leading, trailing, and repeated spaces from a text string, leaving single spaces between words. It is the fastest way to clean up copied or imported text so lookups, comparisons, and reports work correctly.
=TRIM(text)What does TRIM do?
TRIM is a text function that takes a piece of text and returns the same text with all unnecessary spaces removed. It deletes spaces at the start and end of the text and reduces any run of multiple spaces between words to a single space. The result is a clean, predictable string that Excel can match and calculate with.
Extra spaces are one of the most common reasons formulas fail. A name copied from an email might have a trailing space, so VLOOKUP cannot find it. A product code imported from a system might have double spaces in the middle, so a count comes out wrong. TRIM fixes these problems without you having to retype anything.
Imagine you receive a customer list from a colleague. Some names have spaces before them, some have several spaces between first and last names, and one has a space at the end. You need to match these names against your own database. Wrapping the name cell in TRIM gives you a clean value that matches correctly, and the whole cleanup takes one formula.
When to use TRIM
- Cleaning names or addresses pasted from emails, PDFs, or web forms.
- Preparing text for VLOOKUP, XLOOKUP, or COUNTIF so lookups match exactly.
- Tidying product codes or IDs that contain accidental double spaces.
- Removing trailing spaces from data exported from another system.
- Standardising text before combining it with CONCAT or TEXTJOIN.
- Making reports look neat when source text has uneven spacing.
TRIM syntax
=TRIM(text)| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text or cell reference you want to clean. For example, A2 or " Hello ". |
What it returns: A text string with leading and trailing spaces removed and internal runs of spaces reduced to one space.
TRIM examples
1 Remove spaces around a single name
The goal is to clean a name that has spaces before and after it. Cell A2 contains two leading spaces and two trailing spaces. The formula =TRIM(A2) removes all four, leaving just the name. The result is the clean text "Alice Brown".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | |||
| 2 | Alice Brown | Alice Brown | ||
| 3 | Bob Smith | |||
| 4 | Carol Jones | |||
| 5 |
2 Fix double spaces between first and last name
Here the problem is inside the text: the first name and last name are separated by three spaces. =TRIM(A2) keeps one space between the words and removes the rest. The formula returns "John Smith", which is the standard single-space version.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full Name | |||
| 2 | John Smith | John Smith | ||
| 3 | Mary Johnson | |||
| 4 | Peter Brown | |||
| 5 |
3 Clean a product code before matching
A product code in A2 has a leading space and a double space in the middle. The formula =TRIM(A2) removes the leading space and reduces the double space to one. The cleaned code "AB-123" can now be compared with other codes reliably.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Code | |||
| 2 | AB-123 | AB-123 | ||
| 3 | CD-456 | |||
| 4 | EF-789 | |||
| 5 |
4 Prepare a city name for VLOOKUP
The city in A2 is stored as " New York " with spaces on both sides. A lookup against a clean list would fail without TRIM. =TRIM(A2) returns "New York", which matches the lookup table exactly. Use the TRIM result inside VLOOKUP or XLOOKUP for a reliable match.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | City | |||
| 2 | New York | New York | ||
| 3 | Chicago | |||
| 4 | Boston | |||
| 5 |
5 Tidy a full address from an import
An address imported from another system often has extra spaces around commas and between words. Cell A2 contains " 10 Downing St , London ". =TRIM(A2) removes the outer spaces and collapses the internal runs, returning "10 Downing St , London". The comma spacing is preserved because TRIM only changes spaces, not punctuation.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Address | |||
| 2 | 10 Downing St , London | 10 Downing St , London | ||
| 3 | 221B Baker Street, London | |||
| 4 | 5th Avenue, New York | |||
| 5 |
Common TRIM errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The argument is a range of cells rather than a single cell or text value. | Use TRIM on one cell at a time, for example =TRIM(A2), and copy the formula down. |
| #NAME? | The function name is misspelled, such as TRIM M or TRIMM. | Type =TRIM( and let Excel suggest the function, or correct the spelling. |
| No visible change | The spaces are non-breaking spaces (character 160) from a web page, which TRIM does not remove. | Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert non-breaking spaces first. |
| Numbers become text | TRIM always returns text, so a number like 123 becomes the text "123". | Wrap the result in VALUE if you need a number: =VALUE(TRIM(A2)). |
Tips for using TRIM
- Use TRIM on the cell reference, not on a typed string, so the formula updates when the source changes.
- Combine TRIM with CLEAN to remove both extra spaces and non-printing characters.
- Apply TRIM before VLOOKUP, XLOOKUP, or COUNTIF to avoid match failures caused by hidden spaces.
- To clean a whole column permanently, copy the TRIM results and paste them back as values.
- TRIM does not remove non-breaking spaces; use SUBSTITUTE with CHAR(160) for those.
Frequently asked questions
Does TRIM remove all spaces?
No. TRIM removes leading and trailing spaces and reduces multiple spaces between words to one. It keeps the single spaces that separate words.
Why does TRIM not remove some spaces?
Those are likely non-breaking spaces (character 160) from web pages or imported data. TRIM only handles regular spaces (character 32). Use SUBSTITUTE to replace CHAR(160) with a normal space first.
Can TRIM clean numbers?
TRIM returns text, so a number becomes a text string. If you need a numeric result, wrap it in VALUE, for example =VALUE(TRIM(A2)).
Is TRIM case-sensitive?
No. TRIM does not change letter case; it only affects spaces.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TRIM reference