Remove All Spaces in Excel (9 Methods)

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, I’m going to explain how to remove all spaces in Excel.

To make this explanation visible to you I’m going to use a sample dataset. It has 3 columns that represent a person’s information. These columns are Name, Zip Code, and Address.

Download to Practice

9 Ways to Remove All Spaces in Excel

1. Using TRIM Function to Remove All Spaces

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, I will work with the Address column.

First, select the cell where you want to place your resultant value.
➤ I selected the cell E4
Then, type the following formula in the cell or into the Formula Bar.

Finally, press ENTER. 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.

Using the TRIM function you can remove all white spaces from the values of Name and Zip Code.

Read more: How to Remove Spaces in a Cell in Excel

2. Using TRIM for Numeric Values

As the TRIM function treats numeric values also as text strings so to deal with numeric values, I will use the TRIM function with the VALUE function.

In this procedure, I will work with the ZIP Code column.

In the first place, select the cell where you want to place your resultant value.
➤ I selected the cell E4
Then, type the following formula in the cell or into the Formula Bar.

Here, the TRIM function will remove all spaces from the numeric value in C4. Then the VALUE function will convert the text into the Number Format.

In the end, press ENTER.
Eventually, it will remove all spaces of the numeric value and will convert it into a number format.

➤ Now, use the Fill Handle to AutoFill formula for the rest of the cells.

Read more: How to Remove Blank Spaces in Excel

3. Using TRIM with CLEAN to Remove Non-printing Spaces

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, I will work with the Address column.

To begin with, select the cell where you want to place your resultant value.
➤ I selected the cell E4
Then, type the following formula in the cell or into the Formula Bar.

Here, the CLEAN function will delete all the non-printing characters of the D4 cell. Then the TRIM function will remove all spaces from the D4 cell.

Finally, press ENTER. Hence, it will remove all white spaces and non-printing characters from the selected cell.

➤ Now, use the Fill Handle to AutoFill formula for the rest of the cells.

Feel free to use the TRIM and CLEAN functions to remove all white spaces and non-printing characters from the values of Name and Zip Code.

Read more: How to Remove Leading Space in Excel

4. Using SUBSTITUTE Function to 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. For better understanding, I made some changes in the Zip Code column.

Firstly, select the cell where you want to place your resultant value.
➤ I selected cell D4
Then, type the following formula in the cell or into the Formula Bar.

Here, in this formula, C4 is the cell containing text with extra spaces. In the SUBSTITUTE function, I used ” “ (is the spaces I want to remove) as old_text and “” (which means no space) as new_text. So, the function will replace all spaces of C4 with no space.

Finally, hit the ENTER key. Therefore, it will show the result by removing all spaces.

➤ Now, use the Fill Handle to AutoFill formula for the rest of the cells.

5. Using TRIM with SUBSTITUTE 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, I’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.

To begin the procedure, select the cell where you want to place your resultant value.
➤ I selected cell C4
Then, type the following formula in the cell or into the Formula Bar.

Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the B4 cell. Then the TRIM function will remove all white spaces.

Lastly, hit the ENTER key. Eventually, it will show the result by removing all non-breaking spaces and white spaces.

➤ Now, use the Fill Handle to AutoFill formula for the rest of the cells.

Similar Readings:

• How to Remove Extra Spaces in Excel (4 Methods)
• Remove Trailing Spaces in Excel (6 Easy Methods)

6. Using TRIM CLEAN and  SUBSTITUTE to Remove 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, I’ll use the Address column.

First, select the cell where you want to place your resultant value.
➤ I selected cell C4
Then, type the following formula in the cell or into the Formula Bar.

Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the D4 cell. Then the CLEAN function will delete all the non-printing characters of the D4 cell. Finally, the TRIM function will remove all white spaces from the D4 cell.

In the end, press the ENTER key. As a result, it will show the result by removing all non-breaking lines, non-printing characters, and white spaces.

➤ Now, use the Fill Handle to AutoFill formula for the rest of the cells.

If you want, you can use the TRIM, CLEAN, and SUBSTITUTE functions to remove all 3 types of spaces from the values of Name and Zip Code.

7. Using Power Query to Remove All Spaces

You can use the Power Query to remove all spaces in Excel.

To start the procedure,

First, select the cell range.
➤ I selected the cell range B4:D17.
Now, open the Data tab >> then select From Table/Range

A dialog box will pop up showing the selection then select My table has headers. Finally, click OK.

➤ A new window will pop

Here, select the Name column.
Then, right click on the mouse >> from Transform >> select TRIM

Here, it will remove only the leading spaces.

To remove all spaces. Again, on the Name column, right click on the mouse >> select Replace

A dialog box will pop up. From the in  Value To Find give double spaces and in Replace With give single space. Finally, click OK.

Therefore, it will remove all spaces from the Name column.

If you want you can keep it in your current worksheet.

I’ve done it for one particular column; you can do it for the rest of the columns as well.

8. Using Find & Replace to Remove All Spaces

To remove all spaces you also can use the FIND & REPLACE command.

To begin the procedure, select the cell or cell range from where you want to remove the leading spaces
➤ Here, I selected the range B4:B10.
Then, open the Home tab >> from Find & Select >> select the Replace

A dialog box will pop up. Now, you can put how many spaces you want to remove in Find what.
➤ Here, I 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, I gave single spaces.

After putting the spaces in both Find what and Replace with. Then, click Find Next. Finally, press the Replace All.

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. Then, click OK.

Now, you will see all white spaces of the Name column are removed.

9. Using VBA to remove All Spaces

To remove all spaces in Excel you also can use Visual Basic.

First, open the Developer tab >> then select Visual Basic.

Then, it will open Microsoft Visual Basic for Applications.
Now, open Insert >> select Module.

A Module will open then type the following code in the opened Module.

Sub Remove_All_Spaces()
Dim rng As Range
Dim cell As Range
Dim TempCell As String
Set rng = Selection
For Each cell In rng
    TempCell = cell.Value
    TempCell = Trim(TempCell)
    cell.Value = TempCell
Next cell
End Sub

Here, I declared the Sub procedure Remove_All_Spaces where rng and cell are Range type variables, TempCell is String type.
Then, used a For loop to TRIM each selected cell. 

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 B4:B10.
Next, open the View tab >> from Macros >> select View Macros

A dialog box will pop up.

Now, from the Macro name select the Remove_All_Spaces also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will remove all leading spaces of the selected Name column.

➤ Again, I selected the range C4:C10 to Run the Macro.

Now, you will see all the leading spaces will be removed from the Zip Code column.

Things to remember, it only removes leading and trailing spaces, not the mid spaces.

Practice Section

I’ve given a practice sheet in the workbook to practice these explained ways to remove all spaces. You can download it from the above.

In 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.

Further Readings

• Remove Leading Spaces in Excel (4 Methods)
• How to Remove the Trailing Spaces in Excel (2 Easy Ways)