Video: How to remove extra spaces in Excel
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.
How to delete spaces in Excel: video transcript
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.
How to remove leading and trailing spaces 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:
- First you select the range that you want to clean up.
- Find the Trim Spaces icon on the Ablebits Data tab, and select the option to remove leading and trailing spaces.
- You can choose to back up the sheet as you can't cancel any changes made by add-ins, and just click Trim.
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.
How to delete extra space between two words in Excel cells
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.
How to get rid of all spaces in Excel
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:
- Select your range with numbers and press Ctrl+H to open it.
- Enter the space in the "Find what" field, and make sure there is nothing in the "Replace with" field.
- Click "Replace all", and get the numbers you can use for your work.
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 use the Substitute function or the "Replace" dialog box in Excel to delete all blanks from the selected cells;
- Use the Trim function to delete leading and trailing spaces;
- Or run Cell Cleaner to remove any extra characters before, after, or between the words.
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.
Table of contents
