TRIM function
Quick answer
TRIM removes spaces from the start and end of text and shrinks runs of spaces between words to one.
- Syntax
=TRIM(text)- Most common use
=TRIM(A2)returns Maya Chen (Leading spaces removed)
It's the first fix to try when lookups return #N/A or duplicates don't look like duplicates, because imported data is full of invisible spaces.
- Available in
- Every current version
- Category
- Text functions
- Returns
- Text with single spaces between words and none at either end.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=TRIM(text)
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text or cell to clean. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =TRIM(A2) | Maya Chen | Leading spaces removed |
| D3 | =LEN(A3)-LEN(TRIM(A3)) | 2 | How many extra spaces the cell had |
| D4 | =TRIM(C4) | Ships Monday | Runs of spaces between words become one |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Customer (imported) | Phone | Note | Result |
| Maya Chen | 612-555-0148 | Call after 3pm | Maya Chen |
| Raj Patel | 763-555-0193 | VIP | 2 |
| Lee Wong | 952-555-0107 | Ships Monday | Ships Monday |
Practice TRIM
From lesson 20, Cleaning messy data. Graded instantly, then re-checked on changed data.
Clean names
B2, remove extra spaces and convert to Proper Case. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Spaces copied from websites survive | Web pages and PDFs often use the nonbreaking space (character 160), which TRIM doesn't remove. Convert it first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). |
| Lookups still return #N/A | The lookup table may have spaces too. Clean that column as well, or trim inside the lookup: =XLOOKUP(TRIM(A2),TRIM(D2:D9),E2:E9). |
| Numbers stay text | TRIM returns text, so a trimmed " 42 " is "42", not 42. Add 0 or wrap it in VALUE to get a number back. |
Tips
- Highlight cells with extra spaces using a conditional formatting rule such as =LEN(A2)<>LEN(TRIM(A2)).
Questions
Why isn't TRIM removing spaces in Excel?
The spaces are probably nonbreaking spaces (character 160) from a web page or PDF. TRIM only removes regular spaces. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Does TRIM remove the spaces between words?
Only the extras. Two or more spaces between words become one; a single space stays.
Lessons that use TRIM
Answers that use TRIM
In other languages
TRIM is ESPACIOS in Spanish Excel and GLÄTTEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .