Extra spaces are one of the most common reasons that Excel formulas fail. A VLOOKUP does not find a name because it has a space at the end, a count of unique customers is too high because “Ali” and “Ali ” look the same but are different, and numbers pasted from a website refuse to add up. The fix depends on which spaces you have: leading, trailing, double spaces between words, every space, or the stubborn non-breaking space. This guide shows five reliable methods, with working formulas and examples, and explains how to spot and remove the kind of space that TRIM cannot.
Updated November 2026
In this guide
- Why spaces cause problems
- Method 1: the TRIM function
- Method 2: SUBSTITUTE to remove all spaces
- Method 3: Find and Replace
- Method 4: Flash Fill
- Method 5: Power Query for repeat jobs
- Which method to use
- The non-breaking space problem
- Worked example: cleaning a customer list
- Remove spaces from numbers
- Preventing spaces at the source
- Quick recap
- Related cleaning tasks
- Common mistakes
Why spaces cause problems
Excel compares text exactly, so “Sara Ali” and “Sara Ali ” (with a space at the end) are different values. Lookups, duplicate checks, pivot tables and filters treat them as separate items. Spaces also sneak in when you import data from a database, copy from a web page or a PDF, or type fast. A cell that looks fine can hide a space at the start or the end. To check, use =LEN(A2) and compare the result with the number of characters you can see. If the count is higher, there are extra characters. Our guide to VLOOKUP not working lists this as one of the most frequent causes.
Method 1: the TRIM function
TRIM removes spaces from the start and end of the text and reduces repeated spaces between words to a single space. If the messy text is in A2, type =TRIM(A2) in B2 and fill down. For example, ” Sara Ali ” becomes “Sara Ali”. TRIM is the best choice for names, addresses and product titles because it keeps one space between words. It does not remove spaces inside a number such as “1 000”, and it does not remove the non-breaking space from web pages, which is covered below.
Method 2: SUBSTITUTE to remove all spaces
If you want no spaces at all, for example in a code such as “AB 123 45”, use =SUBSTITUTE(A2," ",""). It replaces every space with nothing and returns AB12345. This is handy for phone numbers, IDs and product codes. You can combine it with TRIM when needed, but TRIM is not required in this case because every space is removed. To remove only the first space, add a fourth argument, such as =SUBSTITUTE(A2," ","",1).
Method 3: Find and Replace
For a quick one-time clean-up without formulas, select the cells, press Ctrl+H, type a single space in Find what, leave Replace with empty and click Replace All. This removes every space in the selected cells, including those between words, so use it only for codes and numbers. It changes the original data, so keep a copy first. Find and Replace is useful when a number column contains spaces as thousands separators, such as “12 500”, because after removing the space Excel can often convert the text into a number.
Method 4: Flash Fill
Flash Fill learns from an example. Type the cleaned version of the first value next to it, then start typing the second. Press Enter to accept the gray preview or press Ctrl+E. Flash Fill gives static values, not formulas, so it will not update if the source changes. It is a good option for a one-off job where the pattern is clear, and a poor one when the data has many different layouts.
Method 5: Power Query for repeat jobs
If you clean the same file every week, use Power Query. Select the data, choose Data, then From Table/Range. In the editor, select the text column, go to Transform, then Format, and click Trim. You can also click Clean to remove non-printing characters. Close and Load puts the clean table in a new sheet. Next week, paste the new data in the source and click Refresh All, and Excel repeats the steps.
Which method to use
This table helps you choose.
| Situation | Best method | Formula or step |
|---|---|---|
| Extra spaces around names | TRIM | =TRIM(A2) |
| Spaces inside codes or numbers | SUBSTITUTE | =SUBSTITUTE(A2,” “,””) |
| One-time clean without formulas | Find and Replace | Ctrl+H, space, Replace All |
| Pattern-based clean-up | Flash Fill | Ctrl+E |
| Weekly import | Power Query | Transform, Format, Trim |
The non-breaking space problem
Data copied from web pages often contains a non-breaking space, character 160, which looks like a space but is not removed by TRIM. To fix it, replace character 160 with a normal space and then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). To remove it completely, use =SUBSTITUTE(A2,CHAR(160),""). To check whether a cell has one, use =CODE(MID(A2,1,1)) for the first character or =SUMPRODUCT(--(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=CHAR(160))) for a count. If the TRIM result still has the wrong length, character 160 is the likely cause. For other hidden characters such as line breaks, wrap with CLEAN: =TRIM(CLEAN(A2)).
Worked example: cleaning a customer list
Suppose column A has these customer names: ” Ayesha Khan”, “Bilal Ahmed” (two spaces) and “Sara Ali ” (space at the end). The lengths are 12, 12 and 9, while the clean names have 11, 11 and 8 characters. In B2 type =TRIM(A2) and fill down. The results are “Ayesha Khan”, “Bilal Ahmed” and “Sara Ali” with lengths 11, 11 and 8. Now a VLOOKUP using the clean names in column B finds matches that failed before. When you are happy with the result, copy column B and use Paste Special, then Values, over column A, and delete column B so that the sheet contains only clean data.
Remove spaces from numbers
When a number is stored as text with spaces, it will not add up. For “1 250” in A2, use =VALUE(SUBSTITUTE(A2," ","")), which returns 1250 as a real number. If the result still shows an error, check for character 160 as above. After converting, format the cells as Number and check with =ISNUMBER(B2), which should return TRUE. The same approach works for amounts with a currency symbol if you also substitute the symbol, for example =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$","")," ","")). You can wrap these in an IF function to handle blank cells, as in =IF(A2="","",VALUE(SUBSTITUTE(A2," ",""))).
Preventing spaces at the source
The best fix is to stop bad spaces from entering your sheet. Use Data Validation with a custom rule so that a cell rejects text with a leading or trailing space: select the cells, choose Data, then Data Validation, pick Custom and enter =A2=TRIM(A2) (adjust the reference to the first selected cell). If someone types a space at the end, Excel shows an error and asks for a correction. For imports, clean the data in the same step with Power Query, so you do not forget. When you share a template with a team, add a note at the top reminding people to paste values only, since pasting from web pages and emails brings in hidden characters.
You can also highlight problem cells with conditional formatting. Select the range, choose Conditional Formatting, then New Rule, then use a formula such as =LEN(A2)<>LEN(TRIM(A2)) and pick a fill color. Any cell with extra spaces turns that color, so you can see the problems at a glance before they break a lookup or a report.
Quick recap
Use TRIM for names and sentences, SUBSTITUTE for codes and numbers, Find and Replace for quick one-offs and Power Query for weekly imports. If TRIM does not work, replace character 160 first. Always check with LEN, work on a copy and paste values when you are done. With these steps you can clean a list of thousands of rows in a few minutes and make lookups, counts and pivot tables behave as expected.
Related cleaning tasks
After spaces, check the other usual suspects: mixed capitalization, text numbers and duplicate rows. Our guides on capitalizing text in Excel and the built-in Remove Duplicates tool cover those steps, and together they make imported data reliable for lookups and pivot tables.
Common mistakes
The most common mistake is to use Find and Replace on names, which removes the space between first and last names. Another is to forget that formulas return new cells, so the original column still has the spaces. A third is to ignore character 160. A fourth is to clean the lookup column but not the lookup value, so the match still fails. Finally, keep a backup of the original data before you overwrite it. If you need to put spaces back together later, our guide to combining text in Excel shows how.
Frequently asked questions
How do I remove extra spaces in Excel?
Use =TRIM(A2), which removes leading, trailing and repeated spaces.
How do I remove all spaces in Excel?
Use =SUBSTITUTE(A2,” “,””) or Find and Replace with a single space.
Why does TRIM not remove the space?
The space may be a non-breaking space, character 160. Use SUBSTITUTE(A2,CHAR(160),” “) first.
How do I remove spaces from numbers?
Use =VALUE(SUBSTITUTE(A2,” “,””)) to convert text with spaces to a number.
How can I check for hidden spaces?
Compare =LEN(A2) with the visible character count.
Related guides and free tools
- VLOOKUP not working: 8 fixes
- How to combine text in Excel
- How to transpose in Excel
- COUNTIFS in Excel
- All free calculators
- All free calculators and templates
Sources: IF function, Microsoft Support


