Learn how to delete spaces in Excel using standard functions or a special add-in.
When you want to trim text and remove leading or trailing spaces that impede with sorting and filtering your data, make sure there is one blank space between the values in your cells, or remove spaces between numbers in Excel, you can employ the standard TRIM and SUBSTITUTE functions, use the Replace option, or run Cell Cleaner add-in that was designed to delete any extra spaces. See this video to learn the ways of removing unwanted whitespace in Excel.
When you sort, compare, or calculate records in your tables, spaces at the beginning or at the end of your cells and extra blanks between the values become a problem. In this video we'll look at three common ways you can remove extra spaces using a special tool or formulas in Excel.
One of the first add-ins we created was for this very typical task: removing leading and trailing spaces. Cell Cleaner add-in makes it very easy to get rid of any unwanted characters. Here is how it works:
You can also use the TRIM function in Excel to do this. First you need to create a helper column that you will use for the function. We enter =TRIM, and point to our first cell with data in parenthesis.
Next you need to copy this formula down the column. Thus you'll apply it to all cells below. The quickest way to do it is point to the lower-right corner of the cell with the formula, and double-click the black plus sign that you'll see.
Now, if you want to work with the values, you can copy the results, and replace the formulas using the Paste Special option. Right-click where you want to get the values, and choose to paste special, values only. Now you can remove the column you don't need.
If your task is a bit trickier and you want to remove extra spaces between the words, you can use Cell Cleaner to delete them. Select your data, click on the add-in's icon, and pick the option that trims blanks between words to one. Click Trim and your data will be cleaned up.
When you end up with data like this, and you can't use numbers because of spaces between them, you can use one of standard means to remove all blanks.
One way is using the standard Replace feature in Excel:
The second way you can go is use the Substitute function. Add a column for the formula, and enter =SUBSTITUTE(A2," ","")
Enter A2, the first cell with the number, add a comma, then enter a space in quotes: that's the character you want to replace, comma, and enter quotes without anything inside: our substitution.
What it does, essentially, is replace all spaces in the specified cell with nothing.
Now you need to copy the formula down the column; double-click on the plus sign for the quickest way. Copy the results, and paste special, values to work with them.
Wherever you have unwanted spaces, they won't let you use the data in your calculations. All in all, you have three ways of removing blanks:
You can get 15 % off the add-in if you use the coupon code Preview.
Feel free to post your questions with any particular tasks, and subscribe to our channel for more tips and tricks on Microsoft Excel.