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: =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. Of course, learning a handful of formulas is a good exercise to sharpen your skills, but it might be time-consuming. Excel users who value their time and appreciate convenience can take advantage of the Text Tools included with our Ultimate Suite for Excel. One of these handy tools allows removing spaces and non-printing characters in a button click. Once installed, Ultimate Suite adds several useful buttons to your Excel ribbon such as Trim Spaces, Remove Characters, Convert Text, Clear Formatting, and a few more. Whenever you want to remove blank spaces in your Excel sheets, perform these 4 quick steps: