How to Remove All Spaces in Excel (9 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Dataset to remove all spaces in Excel


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.

Using TRIM Function to remove spaces

  • Finally, all spaces will be removed from the Addresses.

Addresses after removing all spaces

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

Output after removing spaces using TRIM Function

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.

Using TRIM and VALUE Functions to remove spaces

  • Eventually, it will remove all spaces of the numeric value and will convert it into a number format.

Results found after removing all spaces

Here, the TRIM function will remove all spaces from the numeric value in C5. Then the VALUE function will convert the text into the 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.

Using TRIM and CLEAN Functions to remove spaces

  • Hence, it will remove all white spaces and non-printing characters from the cell range.

After removing non-printing spaces from addresses

Here, the CLEAN function will delete all the non-printing characters of the C5 cell. Then the TRIM function will remove all spaces from the C5 cell.
  • Feel free to use the TRIM and CLEAN functions to remove all white spaces and non-printing characters from the values of Name.

Dataset after removing all non-printing spaces

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.

Using SUBSTITUTE Function to remove spaces

  • Finally, it will show the result by removing all spaces.

Results found after removing all spaces from Zip Code

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

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.

Combining TRIM, SUBSTITUTE & CHAR Functions to remove spaces

  • Eventually, it will show the results by removing all non-breaking spaces and white spaces.

Results found after removing non-breaking spaces

Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the B5 cell. Then the TRIM function will remove all 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.

Using TRIM, CLEAN, SUBSTITUTE & CHAR Functions to remove spaces

  • Thus, non-breaking lines, non-printing characters, and white spaces will be removed from the Addresses.

Addresses after removing non-breaking lines, non-printing characters, and white spaces

Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the C5 cell. Then the CLEAN function will delete all the non-printing characters of the C5 cell. Finally, the TRIM function will remove all white spaces from the C5 cell.
  • If you want, you can use the TRIM, CLEAN, and SUBSTITUTE functions to remove all 3 types of spaces from the values of Name.

Dataset after removing non-breaking lines, non-printing characters, and white spaces


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.

Opening dataset in power query editor

  • Now, a dialog box will pop up showing the selection.
  • Then, select My table has headers.
  • Finally, click OK.

Create table box

  • Next, a new window will pop up.

Power Query Editor

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

Selecting TRIM from Transform command

  • Now, it will remove only the leading spaces.

Removing leading spaces

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

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

Replace Values box

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

Removing all spaces from Name column

  • After that, click on Close & Load >> select Close & Load to.

Closing & Loading dataset into specific worksheet

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

Import Data toolbox

  • We’ve done it for one particular column.
  • However, you can do it for the rest of the columns as well.

Dataset after removing spaces using power query editor

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.

Using Find & Replace feature

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

Find and Replace dialog box

  • Now, it will pop a message that how many replacements occurred. Here it will show 16 replacements.
  • In the end, click OK.

Msgbox showing 16 replacements

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

Msgbox showing 6 replacements

  • Again it replaced 1 space.
  • Lastly, click OK.

Msgbox showing 1 replacement

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

After removing all spaces from Name column


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.

Opening Microsoft Visual Basic operator

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

Microsoft Visual Basic operator

  • 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

Inserting Code into the module

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.

Saving code

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

Opening Macros box

  • After that, select Remove_All_Spaces and click on Run.

Macro box

  • Hence, it will remove all leading spaces of the selected Name column.
  • Similarly, you can remove leading spaces from other columns.

Name column after removing spaces using VBA

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.

Practice Section


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!


Further Readings

Shamima Sultana
Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo