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: In cell C2, you replace CHAR (127) with a regular space (" "), and then trim that space: =TRIM (SUBSTITUTE (A2, CHAR (127), " "))
The result should look something similar to this: If your data contains a few different non-printing chars as well as non-breaking spaces, you can nest two or more SUBSTITUTE functions to remove all unwanted character codes at a time: =TRIM (SUBSTITUTE (SUBSTITUTE (A2, CHAR (127), " "), CHAR (160), " ")))
How to remove all spaces in Excel
In some situations, you may want to remove absolutely all white spaces in a cell, including single spaces between words or numbers. For example, when you have imported a numeric column where spaces are used as thousands separators, which makes it easier to read big numbers, but prevents your formulas from calculating. 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: