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: To delete all spaces in one go, use SUBSTITUTE as explained in the previous example, with the only difference that you replace the space character returned by CHAR (32) with nothing (""): =SUBSTITUTE (A2, CHAR (32), "")
Or, you can simply type the space (" ") in the formula, like this: =SUBSTITUTE (A2," ","")
After that, replace formulas with values and your numbers will calculate properly. How to count spaces in Excel
Before removing spaces from a certain cell, you may be curious to know how many of them are actually there. To get the total count of spaces in a cell, do the following: Calculate the entire string length using the LEN function: LEN (A2)
Substitute all spaces with nothing: SUBSTITUTE (A2," ","")
Compute the length of the string without spaces: LEN (SUBSTITUTE (A2," ",""))
Subtract the "space-free" string length from the total length. Assuming the original text string is in cell A2, the complete formula goes as follows: