When you import contents into Excel from other sources, there will be additional spaces in Excel cells. In this article, we will introduce 3 effective methods to remove spaces in cells.
Removing spaces in cells can be annoying because it is difficult to find the exact position. And removing those additional spaces will cause a bunch of time and energy. The image below shows the contents that we have copied from other sources. You will find that there are many additional spaces in this cell.
Removing those cells one by one will surely cost a lot of time especially when there are many cells that contain additional spaces. Therefore, you can use the methods below to remove spaces.
Method 1: Find and Replace
In this method, we will show you to use the “Find and Replace” features to remove spaces.
- Press the button “Ctrl + F” on the keyboard to open the “Find and Replace” window.
- And then click the “Replace”.
- After that, input a space into the text box of “Find what”.
- Next click the button “Find Next”. Thus, the cell with spaces will be selected.
- And then click “Replace” in the window.
Thus, you will find that all the spaces in the cell will be removed.
- Now continue the step 4 and step 5 to remove spaces in other cells.
- When you finish the process, close this window.
You need to pay attention that all the spaces in a cell will be replaces including the spaces between words. If you need to keep the spaces between words, you can use other methods.
Method 2: Excel Functions
Here you can also use Excel functions in Excel to remove spaces.
- Click a blank cell in the worksheet.
- And then input the following formula into the cell:
=TRIM(A1)
In this formula, the cell A1 is the target cell that contains additional spaces.
- Next press the button “Enter” on the keyboard. You will see that the result of the cell only contains the necessary spaces. All the additional spaces have been removed.
On the other hand, if you need to remove all the spaces in target cells, you can use another formula:
=SUBSTITUTE(A1,” “,””)
The second argument in this formula is a space, and the third argument is nothing. Thus, this formula will remove all the spaces in a cell.
For the above two formulas, you can choose one formula according to your need.
Method 3: Excel Macros
Except for the above two methods, you can also use Excel macros to remove spaces.
- Press the button “Alt + F11” on the keyboard to open the Visual Basic editor.
- And then click the button “Insert” in the toolbar.
- In the sub menu, choose the option “Module”. Thus, you have inserted a new module in the project.
- In this step, copy the following codes and paste into the module.
Sub RemoveAllSpaces()
Range("B1") = Replace(Range("A1"), " ", "")
End Sub
This macro will remove all the spaces in a cell. You can also change the range in the codes according to your need. Besides, you can directly produce the result in the current cell if you need.
- After that, click the button “Run Sub” in the toolbar to run this macro. On the other hand, you can also press the button “F5” on the keyboard to run macro.
Next you can come back to the worksheet and check the result. You will find that all the spaces are removed.
On the other hand, if you need to keep the spaces between words, you can use the worksheet function in VBA codes.
Sub RemoveAdditionalSpaces()
Range("B2") = Application.WorksheetFunction.Trim(Range("A1"))
End Sub
After you run the macro, you will see that the necessary spaces are still in cells.
A Comparison between the Three Methods
The table below shows the advantages and disadvantages of the above methods.
|
Comparison |
Find and Replace | Excel Functions |
Excel Macros |
|
Advantage |
1. The result will immediately appear in the current cells.
2. This method is easy to understand, and almost all the Excel users can apply this method. |
1. There are two different formulas that you can choose according to your need.
2. If you are not familiar with VBA macros, you can use functions to solve the problem. |
1. You can either remove all spaces or just remove the additional spaces according to your need.
2. By modifying the range in codes, the result can be produced in your target cells. |
|
Disadvantage |
You will remove all the spaced in a cell. This is inconvenient if you need to keep the necessary spaces. | Formulas will produce the result in another cell, and you still need to copy back to the original cell. | Using VBA codes will complicate task that could be easily solved by other methods. |
When you don’t know which method you can choose, you can come back and refer to this table. Thus, you can find the most suitable method according to your actual need.
What to do When You are Bombarded by Virus
When you enter a malicious website or you receive a suspicious file, your computer will probably get affected by virus. When you are bombarded by virus, you need to take actions. Some anti-virus software can come to help. However, you will probably lose some data and information in your Excel files. To repair Excel xls data damage and get back your data, you can use our potent software. This tool is sure to help you repair your precious files.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc error and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply