In this article, we will learn to remove spaces in excel with the formula. Spaces create many problems when we try to execute any formula in the excel worksheet. Sometimes, when we copy the data and paste it into our excel sheet, extra spaces may occur unintentionally. It can produce incorrect results or errors. We will now demonstrate some methods to overcome this problem.
Download Practice Book
Download the practice book.
5 Ways to Remove Spaces with Excel Formula
1. Use of Trim Formula to Remove Spaces in Excel
Excel has a built-in formula that removes spaces from texts. It is the Trim formula. We will use a dataset of two columns to explain this method. These are Employee & ID Number. We will use the same dataset in all methods in this article.
Follow the steps below to learn this method.
- In the beginning, we need to create a helper column. We named it ‘TRIM’ in our dataset.
- Now, select Cell D5 and type the formula in the helper column.
Here, after typing the function, we need to select the cell from where we need to remove spaces.
- Next, hit Enter to see the result.
- Then, use the Fill Handle to see results in all cells.
- After that, select Cell D5 and copy it.
- Now, paste only the value in Cell B5.
- Finally, after doing ‘Copy & Paste’ in all cells, delete the helper column.
2. Remove All Spaces Using Excel SUBSTITUTE Function
Pay attention to the steps to know more.
- Firstly, create a Helper Column & type the formula.
Here, this formula will replace spaces (second argument) with the empty string (third argument).
- Secondly, press Enter to see the result.
- Now, use the Fill Handle to see results in the Helper Column.
Here, we can see there is no space between the first name and the last name of the employees. We can certainly fix this problem by using the TRIM function at the beginning of the SUBSTITUTE function.
- Put the formula in Cell D5.
Here, the SUBSTITUTE function replaces the non-breaking spaces, CHAR(160) with normal spaces, CHAR(32). The TRIM function removes the extra spaces here. We need to add it in front of the SUBSTITUTE function.
- Hit Enter to see the result.
- Finally, use the Fill Handle for the rest of the cells.
3. Excel Formula with MID Function to Remove Leading Spaces
Observe the steps below to know about this method.
- Create a Helper Column at first.
- Now, type the formula in Cell D5.
This formula will find the text and its length at first. The FIND function will return the position of the text string as a number and the LEN function will count the length of Cell B5. Then, it will trim the leading spaces from the text.
- Next, press Enter. You can see in Cell D5 that there is no leading space. But it has spaces between texts.
- Finally, drag the Fill Handle down to see results in the Helper Column.
- How to Remove Spaces in a Cell in Excel (5 Methods)
- Remove Space in Excel before Numbers (3 Ways)
- How to Remove Blank Spaces in Excel (7 Ways)
- How to Remove Space in Excel after Text (6 Quick ways)
4. Apply VBA to Remove Extra Spaces in Excel
Follow the steps for this technique.
- In the first place, go to the Developer tab and select Visual Basic.
- Next, go to Insert in the Visual Basic window and then select Module.
- Type the code in the Module and save it.
Sub Remove_Spaces() Dim x As Range Dim Cells As Range Dim TCell As String Set x = Selection For Each Cells In x TCell = Cells.Value TCell = Trim(TCell) Cells.Value = TCell Next Cells End Sub
- After that, select the range of cells where you want to apply the VBA Here, we have selected Cell B5 to Cell B9.
- Then, select Macros from the Developer.
- Furthermore, select Run from the Macro.
- Finally, you will see results like the picture below.
5. Insert Excel Formula to Delete Spaces between Numbers
Sometimes, we need to clean spaces between numbers. In this section, we will show how we can remove spaces between numbers. We will use the same dataset here. But, we will have spaces in the ID Number column this time.
Follow the steps below.
- Firstly, create an extra column. The Helper Column is the extra column here.
- Secondly, select Cell D5 and enter the formula.
- Thirdly, press Enter and use the Fill Handle in the Helper Column.
- Alternatively, you can do this with the ‘Find & Replace’ Select the range of cells from where you want to remove spaces.
- Next, press Ctrl + H from the keyboard. The ‘Find And Replace’ window will occur.
- Press the Space bar in the ‘Find what’ section and keep the ‘Replace with’ section empty.
- Finally, select Replace All to see the results.
Things to Remember
We have discussed some easy methods about how to trim spaces in excel. There is an important thing we need to remember while we are using these methods. We must create an extra column at first for Method-1,2 & 3. After performing the steps, we need to replace the main data with the trimmed data. We can do this using Copy & Paste. Make sure to paste only values. This procedure is shown in Method-1.
We have described 5 methods to erase spaces from our excel worksheet. These are mainly formula-based methods. You can also use the ‘Find & Replace’ option which is discussed in the last method. Furthermore, I hope these methods will help you to solve your problem completely. Last of all, if you have any queries or suggestions, feel free to comment below.