How to Remove Spaces in Excel: TRIM, SUBSTITUTE, Find and Replace

shape
shape
shape
shape
shape
shape
shape
shape
How to Remove Spaces in Excel: TRIM, SUBSTITUTE, Find and Replace - featured image

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

  1. Why spaces cause problems
  2. Method 1: the TRIM function
  3. Method 2: SUBSTITUTE to remove all spaces
  4. Method 3: Find and Replace
  5. Method 4: Flash Fill
  6. Method 5: Power Query for repeat jobs
  7. Which method to use
  8. The non-breaking space problem
  9. Worked example: cleaning a customer list
  10. Remove spaces from numbers
  11. Preventing spaces at the source
  12. Quick recap
  13. Related cleaning tasks
  14. 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.

SituationBest methodFormula or step
Extra spaces around namesTRIM=TRIM(A2)
Spaces inside codes or numbersSUBSTITUTE=SUBSTITUTE(A2,” “,””)
One-time clean without formulasFind and ReplaceCtrl+H, space, Replace All
Pattern-based clean-upFlash FillCtrl+E
Weekly importPower QueryTransform, 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

Sources: IF function, Microsoft Support

Leave a Reply

Your email address will not be published. Required fields are marked *