Clean extra spaces
| A | |
|---|---|
| 1 | Name |
| 2 | John Smith |
Formula: C1: =TRIM(A2)
Result: John Smith
Leading and trailing spaces are removed and the three inner spaces become one.
Text
TRIM
Removes extra spaces.
Syntax
TRIM(text)Example: =TRIM(A2)
Available in: All versions
TRIM removes leading and trailing spaces from text and reduces any run of spaces inside to a single space: =TRIM(text). It is the first step in cleaning imported data before lookups or counts.
When to use it
- A lookup returns #N/A even though the value appears to be there.
- Data pasted from the web, a database or a PDF has uneven spacing.
- You need names or addresses with consistent single spaces.
- You compare two columns and want a fair match.
Arguments explained
- text. The text to clean, usually a cell reference. TRIM removes leading and trailing spaces and reduces runs of spaces between words to a single space.
- What it misses. The non-breaking space (character 160) that often comes from web pages is not removed. Use SUBSTITUTE(A2,CHAR(160)," ") first and then TRIM.
- Result type. The output is text. If the cleaned value is a number, wrap the formula in VALUE or multiply by 1 to turn it back.
Worked examples
Length after cleaning
| A | |
|---|---|
| 1 | Name |
| 2 | John Smith |
Formula: C1: =LEN(TRIM(A2))
Result: 10
The raw text has 16 characters; the cleaned text has 10.
Make a lookup work
| A | B | C | |
|---|---|---|---|
| 1 | Name | Staff | |
| 2 | Ben | Ana | |
| 3 | Ben | ||
| 4 | Cara |
Formula: E1: =MATCH(TRIM(A2),C2:C4,0)
Result: 2
Without TRIM, " Ben " would not match "Ben" and MATCH would return #N/A.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =TRIM(A4)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Other | Result | |
| 2 | Alice Johnson | alice johnson | extra spaces | |
| 3 | [email protected] | [email protected] | ||
| 4 | extra spaces | |||
| 5 | SKU-2026-0415 | |||
| 6 | iced coffee, tea, sugar | |||
| 7 | Order 417 shipped |
Result in D2: extra spaces
Common errors and how to fix them
- Non-breaking spaces remain. Web data often contains CHAR(160), which TRIM ignores. Use SUBSTITUTE(A2,CHAR(160)," ") first.
- Result is text. Numbers with stray spaces come back as text. Multiply by 1 or use VALUE.
- Line breaks stay. TRIM does not remove line breaks. Use CLEAN as well.
- Formula leaves the original. TRIM is a formula in a new cell. Paste values over the source if you want a permanent fix.
Related functions
Frequently asked questions
- Why does TRIM not work on my data?
- The spaces are probably non-breaking (CHAR(160)). Replace them with SUBSTITUTE before TRIM.
- Does TRIM remove all spaces?
- No. It leaves a single space between words. Use SUBSTITUTE(A2," ","") to remove all of them.
- Can TRIM clean a whole column?
- In Microsoft 365, =TRIM(A2:A100) spills a cleaned column.
- Does TRIM change the original cell?
- No. It returns a new value.