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: =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: =TRIM (CLEAN ( (SUBSTITUTE (A2,CHAR (160)," "))))
The following screenshot demonstrates the difference: How to delete a specific non-printing character
If the liaison of 3 functions discussed in the above example (TRIM, CLEAN and SUBSTITUTE) was not able to eliminate spaces or non-printing characters in your sheet, it means those characters have ASCII values other than 0 to 32 (non-printing characters) or 160 (non-breaking space). In this case, use the CODE function to identify the character value, and then employ SUBSTITUTE to replace it with a regular space and TRIM to remove the space. Assuming the spaces or other undesirable characters that you want to get rid of reside in cell A2, you write 2 formulas: In cell B2, detect the problematic character value by using one of the following CODE functions: