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: Leading space or non-printing character at the beginning of the string: =CODE (LEFT (A2,1))
Trailing space or non-printing character at the end of the string: =CODE (RIGHT (A2,1))
Space or non-printing character in the middle of the string, where n is the position of the problematic character: =CODE (MID (A2, n, 1)))
In this example, we have some unknown non-printing character in the middle of the text, in the 4 th position, and we find out its value with this formula: =CODE (MID (A2,4,1))
The CODE function returns value 127 (please see the screenshot below). 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.