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: 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: =LEN (A2)-LEN (SUBSTITUTE (A2," ",""))
To find out how many extra spaces are in the cell, get the text length without extra spaces, and then subtract it from the total string length: =LEN (A2)-LEN (TRIM (A2))
The following screenshot demonstrates both formulas in action: Now that you know how many spaces each cell contains, you can safely delete extra spaces using the TRIM formula. Formula-free way to remove spaces and clean data
As you already know, many extra spaces and other unwelcome characters can lurk unnoticed in your sheets, especially if you import your data from external sources. You also know how to delete spaces in Excel with a formula.