Remove Leading, Trailing, and Extra Spaces with TRIM
=TRIM(A2)TRIM removes leading spaces, trailing spaces, and repeated spaces between words. It leaves one normal space between words, which is usually what you want for names, cities, categories, and descriptions.
Remove Non-Printing Characters with CLEAN
=CLEAN(A2)CLEAN removes many invisible characters that come from copied web pages, exports, and old systems. For messy imported data, combine it with TRIM:
=TRIM(CLEAN(A2))Remove Non-Breaking Spaces
Some spaces are not normal spaces. Web pages often use non-breaking spaces, which TRIM does not remove. Use this formula:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Remove All Spaces
If you want to remove every space, including spaces between words or ID parts, use SUBSTITUTE:
=SUBSTITUTE(A2," ","")This is useful for product codes, phone numbers, account numbers, and IDs where spaces should not exist.
Find and Replace Method
Press Ctrl + H, type a space in Find what, leave Replace with blank, and click Replace All. This removes all normal spaces. Be careful: it also removes spaces inside names and sentences, so it is best for IDs and codes, not general text.
Power Query Method
Power Query is best for repeatable cleanup. Load the data into Power Query, select the column, then use Transform →Format →Trim and Clean. When new data arrives, refresh the query instead of repeating manual cleanup.
Why Spaces Break Excel Formulas
Excel treats "ABC" and "ABC " as different text values. That means lookup formulas miss matches, COUNTIF returns lower counts, SUMIF returns zero, and duplicate checks fail. Removing spaces is one of the highest-impact data cleaning steps before analysis.
Recommended Cleaning Formula
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))This handles normal extra spaces, many non-printing characters, and non-breaking spaces in one formula. After applying it, copy the cleaned column and paste values if you want to replace the original data.