TRIM function
TRIM removes spaces from the start and end of text and shrinks runs of spaces between words to one. 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 15, 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
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.