Shamima Sultana
A large worksheet may contain several types of spaces in the contents. To make an outstanding worksheet it is needed to remove all spaces. In this article, we are going to explain how to remove all spaces in Excel.
Download Practice Workbook
You can download the workbook to practice yourself.
9 Suitable Ways to Remove All Spaces in Excel
To make this explanation visible to you we are going to use a sample dataset. It has 3 columns that represent a person’s information. These columns are Name, Zip Code, and Address.
1. Use TRIM Function Remove All Spaces in Excel
To remove all spaces in Excel you can use the TRIM function but remember that the TRIM function treats all values as a string.
Let’s begin the procedure. Here, we will work with the Address column.
Steps:
- Firstly, select the cell where you want to place your resultant value. Here, we will select Cell D5.
- Then, type the following formula in the cell or into the Formula Bar and press Enter.
=TRIM(C5)- Thus, it will show the result by removing all extra spaces.
- Now, use the Fill Handle to AutoFill formula for the rest of the cells.
- Finally, all spaces will be removed from the Addresses.
- Using the TRIM function you can remove all white spaces from the values of Name and Zip Code.
Read More: How to Remove Blank Spaces in Excel (7 Ways)
2. Apply TRIM & VALUE Functions to Remove Spaces from Numeric Values
As the TRIM function treats numeric values also as text strings so to deal with numeric values, we will use the TRIM function with the VALUE function.
In this procedure, we will work with the ZIP Code column.
Steps:
- In the first place, select the cell where you want to place your resultant value. Here, we selected Cell D5.
- After that, insert the following formula and press Enter.
=TRIM(VALUE(C5))- Next, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Eventually, it will remove all spaces of the numeric value and will convert it into a number format.
Read More: How to Remove Space after Number in Excel (6 Easy Ways)
3. Remove Non-printing Spaces Using TRIM & CLEAN Functions in Excel
While importing data from external sources, there is a possibility to come various non-printing characters like carriage return, line feed, vertical or horizontal tab, non-breaking space, Pilcrow, Soft hyphen, Page break, etc.
By using the TRIM function, you can remove white spaces, but it is unable to remove non-printing characters. Excel TRIM is designed to only delete value 32 in the 7-bit ASCII system, which is the space character. But the CLEAN function can delete any and all of the first 32 non-printing characters in the 7-bit ASCII set (values 0 through 31) including line break.
So, you can use the TRIM and CLEAN functions together to solve the non-printing character’s problem.
To demonstrate the procedure, we will work with the Address column.
Steps:
- To begin with, select Cell D5 and insert the following formula.
=TRIM(CLEAN(C5))- Then, press Enter and use the Fill Handle to AutoFill formula for the rest of the cells.
- Hence, it will remove all white spaces and non-printing characters from the cell range.
- Feel free to use the TRIM and CLEAN functions to remove all white spaces and non-printing characters from the values of Name.
Read More: How to Remove Spaces in Excel with Formula (5 Quick Ways)
4. Employ Excel SUBSTITUTE Function Remove All Spaces
You can use the SUBSTITUTE function to remove all spaces in specific cells.
Let’s start the procedure. In the dataset to show how the SUBSTITUTE function works, we will use the Zip Code column.
Steps:
- Firstly, select the cell where you want to place your resultant value. Here, we selected Cell D5.
- Then, type the following formula in the cell or into the Formula Bar and press Enter.
=SUBSTITUTE(C5," ","")- Now, use the Fill Handle to AutoFill formula for the rest of the cells.
- Finally, it will show the result by removing all spaces.
Read More: How to Remove Space Before Text in Excel (4 Methods)
5. Combine TRIM, SUBSTITUTE & CHAR Functions to Remove Non-Breaking Spaces
In any circumstances, if you extract values from a website that is made up of the character CHAR (160) then there exists a possibility of coming from non-breaking spaces.
For that, you can use the SUBSTITUTE function to remove non-breaking leading spaces with normal space characters.
Here, we’ve taken a simple Book-Author dataset, where, in the Book with Author Name column, extracted some values from a website while it came with non-breaking spaces.
Now, we will combine the TRIM, SUBSTITUTE and CHAR functions to remove these non-breaking spaces.
Steps:
- In the beginning, select Cell C5 and insert the following formula.
=TRIM(SUBSTITUTE(B5, CHAR(160), ""))- After that, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Eventually, it will show the results by removing all non-breaking spaces and white spaces.
Read More: How to Remove Extra Spaces in Excel (4 Methods)
6. Use TRIM, CLEAN & SUBSTITUTE Functions to Eliminate All Spaces
In case you extract values from a website that is made up of the character CHAR (160) then there exists a possibility of coming non-breaking spaces and non-printing characters.
For that, you can use the TRIM function within the CLEAN and SUBSTITUTE function to remove non-breaking spaces with normal space characters and non-printing characters.
To begin the procedure, we’ll use the Address column.
Steps:
- Firstly, select the cell where you want to place your resultant value. Here, we selected Cell D5.
- Then, type the following formula in the cell or into the Formula Bar.
=TRIM(CLEAN((SUBSTITUTE(C5,CHAR(160)," "))))- Next, press the ENTER key.
- As a result, it will show the result by removing all non-breaking lines, non-printing characters, and white spaces.
- Lastly, use the Fill Handle to AutoFill formula for the rest of the cells.
- Thus, non-breaking lines, non-printing characters, and white spaces will be removed from the Addresses.
- If you want, you can use the TRIM, CLEAN, and SUBSTITUTE functions to remove all 3 types of spaces from the values of Name.
7. Remove All Spaces Using Power Query Editor in Excel
You can use the Power Query to remove all spaces in Excel. Here are the steps to do that.
Steps:
- In the beginning, select cell range B4:D11.
- After that, go to the Data tab >> select From Table/Range.
- Now, a dialog box will pop up showing the selection.
- Then, select My table has headers.
- Finally, click OK.
- Next, a new window will pop up.
- Here, select the Name column.
- Then, right click on the mouse >> from Transform >> select TRIM.
- Now, it will remove only the leading spaces.
- Again, to remove all spaces click on the Name column, right click on the mouse >> select Replace Values.
- Next, a dialog box will pop up.
- Then, in the Value To Find give double spaces, and in Replace With give a single space.
- Lastly, click OK.
- Therefore, it will remove all spaces from the Name column.
- After that, click on Close & Load >> select Close & Load to.
- Now, the Import Data box will open.
- Then, select Existing worksheet if you want you can keep it in your current worksheet and insert the cell where you want to put the data.
- Finally, click on OK.
- We’ve done it for one particular column.
- However, you can do it for the rest of the columns as well.
Read More: How to Remove Spaces in Excel: With Formula, VBA & Power Query
8. Apply Find & Replace Feature to Remove All Spaces
To remove all spaces, you also can use the FIND & REPLACE command.
Steps:
- To begin the procedure, select the cell or cell range from where you want to remove the leading spaces.
- Here, we selected the range B5:B11.
- Then, open the Home tab >> from Find & Select >> select Replace.
- Now, a dialog box will pop up. Now, you can put how many spaces you want to remove in Find what. Here, we gave double spaces.
- After that, you will need to put in how many spaces you want to replace the existing spaces in Replace with. Here, we gave single spaces.
- Finally, press the Replace All.
- Now, it will pop a message that how many replacements occurred. Here it will show 16 replacements.
- In the end, click OK.
- In case there exist more spaces in your selected cell then you can click on Replace All again. It will remove the existing spaces.
- Now, it has replaced 6 spaces.
- Again, click OK and Replace All.
- Again it replaced 1 space.
- Lastly, click OK.
- Finally, you will see all white spaces of the Name column are removed.
9. Use VBA to Delete All Spaces in Excel
To remove all spaces in Excel you also can use Visual Basic.
Steps:
- Firstly, open the Developer tab >> then select Visual Basic.
- Then, it will open Microsoft Visual Basic for Applications.
- Now, open Insert >> select Module.
- Next, a Module will open then type the following code in the opened Module.
Sub Remove_All_Spaces()
Dim cell_rng As Range
Dim cell_value As Range
Dim TempCell_value As String
Set cell_rng = Selection
For Each cell_value In cell_rng
TempCell_value = cell_value.Value
TempCell_value = Trim(TempCell_value)
cell_value.Value = TempCell_value
Next cell_value
End Sub
Code Breakdown
- To start with, we declared the Sub procedure named Remove_All_Spaces where cell_rng and cell_value are Range type variables, TempCell_value is String type.
- Then, we used a For Next loop to go through each cell in the cell range.
- Next, I used the Trim function to return value without leading or trailing spaces.
- Lastly, I ended the Sub procedure.
- Finally, Save the code and go back to the worksheet.
- Then, select the cell or cell range to apply the VBA.
- Here, I selected the range B5:B11.
- Next, open the Developer tab >> select Macros.
- After that, select Remove_All_Spaces and click on Run.
- Hence, it will remove all leading spaces of the selected Name column.
- Similarly, you can remove leading spaces from other columns.
Things to remember, it only removes leading and trailing spaces, not the mid spaces.
Practice Section
We’ve given a practice sheet in the workbook to practice these explained ways to remove all spaces. You can download it from the above.
Conclusion
In this article, I explained 9 easy and quick ways to remove all spaces in Excel. These different ways will help you to remove all spaces from both text and numeric values, non-printing characters, and non-breaking lines. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below. And visit ExcelDemy for many more articles like this. Thank you!
