Title: How to remove blank spaces in Excel - leading, trailing, non-breaking
Headings: How to remove spaces in Excel - leading, trailing, non-breaking
How to remove spaces in Excel - leading, trailing, non-breaking
How to remove blank spaces in Excel - leading, trailing, between words
=TRIM (A2)
How to delete line breaks and nonprinting characters
=TRIM (CLEAN (A2))
How to remove non-breaking spaces in Excel
=TRIM (SUBSTITUTE (A2, CHAR (160), " "))
=TRIM (CLEAN ( (SUBSTITUTE (A2,CHAR (160)," "))))
How to delete a specific non-printing character
=TRIM (SUBSTITUTE (SUBSTITUTE (A2, CHAR (127), " "), CHAR (160), " ")))
How to remove all spaces in Excel
=SUBSTITUTE (A2, CHAR (32), "")
=SUBSTITUTE (A2," ","")
How to count spaces in Excel
=LEN (A2)-LEN (SUBSTITUTE (A2," ",""))
=LEN (A2)-LEN (TRIM (A2))
Formula-free way to remove spaces and clean data
You may also be interested in

Content: Clicking the Replace All button will swap all line breaks in the selected range for spaces. Use the following formula to substitute Carriage Return (value 13) and Line Feed (value 10) characters with spaces: =SUBSTITUTE (SUBSTITUTE (A2, CHAR (13)," "), CHAR (10), " ")
For more information, please see How to remove carriage returns (line breaks) in Excel. How to remove non-breaking spaces in Excel
If after using the TRIM & CLEAN formula some stubborn spaces are still there, most likely you copy/pasted the data from somewhere and a few non-breaking spaces sneaked in. To get rid of nonbreaking spaces (html character  ), replace them with regular spaces, and then have the TRIM function remove them: =TRIM (SUBSTITUTE (A2, CHAR (160), " "))
To better understand the logic, let's break down the formula: A non-breaking character has value 160 in the 7-bit ASCII system, so you can define it by using the CHAR (160) formula. The SUBSTITUTE function is used to turn non-breaking spaces into regular spaces. And finally, you embed the SUBSTITUTE statement into the TRIM function to remove the converted spaces. If your worksheet also contains non-printing characters, use the CLEAN function together with TRIM and SUBSTITUTE to get rid of spaces and unwanted symbols in one fell swoop: