Text cleanup
How to Remove Extra Spaces in Excel (TRIM, CLEAN and Non-Breaking Spaces)
Use =TRIM(A2) to remove spaces at the start and end and reduce repeated spaces between words to one. If the text still looks wrong, it contains non-breaking spaces or invisible characters, which TRIM ignores. Use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) instead.
Extra spaces make "John Smith" and "John Smith " look identical and behave differently. Lookups fail, duplicates are missed, sorting puts the odd one out and emails bounce. This guide shows how to remove every kind of stray space.
Step 1: TRIM for ordinary spaces
=TRIM(A2)TRIM removes leading and trailing spaces and replaces any run of spaces between words with a single space. Fill the formula down, copy the results, and Paste Special, Values, over the original column.
Step 2: the space TRIM cannot see
Web pages, PDFs and many exports use a non-breaking space, character 160. It looks like a space but TRIM only removes the normal space (character 32), so the text seems untouched. Replace it first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))To test whether a cell has one: =ISNUMBER(FIND(CHAR(160),A2)).
Step 3: CLEAN for invisible characters
Line breaks and other non-printing characters (the first 32 characters of the ASCII table) can hide in imported text. CLEAN removes them:
=CLEAN(A2)The combined formula for everything above:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))Where each tool helps
| Symptom | Cause | Use |
|---|---|---|
| Space before or after the text | Typed or pasted with spaces | TRIM |
| Two spaces between words | Careless typing | TRIM |
| TRIM does nothing, but the length is too long | Non-breaking space | SUBSTITUTE with CHAR(160) |
| Words run together or the text jumps lines | Line break inside the cell | CLEAN, or SUBSTITUTE with CHAR(10) |
| A strange box or question mark | Non-printing character | CLEAN |
To see how many characters a cell really has, compare =LEN(A2) with =LEN(TRIM(A2)): any difference is stray spaces.
Find and Replace for a quick fix
Press Ctrl+H. In "Find what", type two spaces; in "Replace with", type one space; click Replace All, and repeat until nothing changes. This does not remove spaces at the start or end of a cell, so TRIM is better.
Warning: do not remove all spaces from columns where they matter, such as names ("Mary Ann") or addresses. Only remove the extra ones.
Spaces inside numbers and phone numbers
1 234 is text, not a number, until the space goes: =VALUE(SUBSTITUTE(A2," ","")). For phone numbers, see fixing phone numbers in Excel, because removing every space is only part of that job.
A whole file at once
DecodingPro trims every text cell in the file, handles the non-breaking spaces, and lists every change so you can see exactly what was touched.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Why is TRIM not working on my data?
The spaces are probably non-breaking spaces (character 160) copied from a website or PDF. Wrap the cell in SUBSTITUTE(A2,CHAR(160)," ") before TRIM.
Does TRIM change numbers?
It turns a number into text, because the result of TRIM is always text. Use VALUE around it if you need a number back.
Can TRIM remove a line break?
No. Use CLEAN, or SUBSTITUTE(A2,CHAR(10)," ") to replace the line break with a space.