How to Remove Blank Characters in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Excel is one of the most widely used applications in our professional life. We organize, calculate, and manipulate our data using Excel. In this article, we are going to explain how to remove blank characters in Excel. When we work with data, we may need to copy data from one place to another. That time some errors occur like adding unnecessary spaces, non-printable characters, non-breaking spaces, lines break, etc. We will discuss the methods to remove these blank characters in this article. Here is our overview image of removing blank characters in Excel.
Overview image

Version

  • These functions are available from Microsoft Excel 2007.
  • Here, we will use Microsoft Excel 365.

How to Remove Blank Characters in Excel: 5 Easy Ways

To explain this, we take the data set of Area code, area name, and short name. There are several unnecessary spaces among words or characters. We have to remove those spaces to retrieve all texts in a proper format by using 5 easy ways. So, to know all these ways, you can follow these methods accordingly.

Easy Ways to Remove Blank Characters in Excel


Method 1: Using the Find and Replace Command to Remove Blank Characters

We can remove blank characters easily by using the Find and Replace feature in Excel. Let’s go through the following steps to see how this method works out perfectly!

Steps:

  • Here, we will select the cells from which data we want to remove the blank spaces.
  • Firstly, go to the Home tab.
  • Secondly, select Editing from the group of commands.
  • Thirdly, from the drop-down, select Find & Select.
  • Then, from the next drop-down, select Replace.
  • Or we can press Ctrl+H together to open the window.

Using Find and Replace Command to Remove Blank Characters in Excel

  • So, we get a dialog box named Find and Replace. Put a space in the Find what.
  • Besides, keep empty the Replace with box.
  • Then, press Replace All.

  • Now, we will get a new Pop-Up. Here shows how many replacements are made.
  • Therefore, press OK on this window.

  • Afterward, press Close on the Find and Replace.

  • Finally, return to your original dataset in the worksheet. And we can see no blank characters in the data table anymore.

Using Find and Replace Command to Remove Blank Characters in Excel

Read More: How to Remove Non-Printable Characters in Excel 


Method 2: Employing TRIM Function to Remove Blank Characters in Excel

We can use the TRIM function to keep appropriate blank characters or spaces in a sentence. In the previous method, we have seen that all the blank characters are removed. So, it doesn’t present a sentence properly.

This TRIM function removes all blank characters from a text string, except single spaces between the words. It keeps single spaces as it’s needed to present a sentence properly.

Steps:

  • Firstly, go to cell C5.
  • Secondly, type the following formula.

=TRIM(B5)

  • Then, press ENTER.

Employing TRIM Function to Remove Blank Characters in Excel

  • After that, pull the Fill Handle icon from cell C5 to C8.
  • Finally, we get the result for the rest of the cells. This function is useful when there is no line break or non-printable letters.


Method 3: Combining TRIM and CLEAN Functions to Remove Blank Characters in Excel

Here we will combine the CLEAN function with TRIM. This CLEAN function can remove unwanted line breaks and blank characters. It mainly removes non-printable characters from the data. When we copy data from one file to another, some non-printable characters appear. This CLEAN function can remove those unnecessary characters.

We modified the data and added some line breaks and non-printable characters. Use Alt+ENTER to add line breaks. We add CHAR(7) as the non-printable character.

Steps:

  • Firstly, choose cell C5.
  • Secondly, type the following formula.

=TRIM(CLEAN(B5))

  • After that, press Enter.

Combining TRIM and CLEAN Functions to Remove Blank Characters in Excel

  • Finally, pull the Fill Handle icon from Cell C5 to C8 to get all the results.


Method 4: Merging SUBSTITUTE, TRIM, and CLEAN Functions to remove Blank Characters

From the previous method, we’ve learned how to remove the non-printable characters and blank characters. Now, we will show how to remove non-breaking spaces. For this, we will add a non-breaking space in our data set. We will use the SUBSTITUTE function. The non-breaking space is marked in the following image.

Steps:

  • Firstly, go to cell C5.
  • Secondly, write down the following combination of TRIM, CLEAN, and SUBSTITUTE functions.
  • Then, type CHAR(160) as old text and new text as “”. So, the formula becomes:

=TRIM(CLEAN(SUBSTITUTE(B5,CHAR(160),"")))

  • After that, press ENTER.

Merging SUBSTITUTE, TRIM, and CLEAN Functions to remove Blank Characters in Excel

  • Next, pull down the Fill Handle icon to C8 to all the results.
  • From the result, we see that non-breaking spaces are removed. We used CHAR(160), as the non-breaking space ASCII code is 160.


Method 5: Using Excel Power Query to Remove Blank Characters

We can remove blank spaces using Power Query. We will apply the Trim command in the power query. But this will not remove all blank characters. It mainly removes leading blank characters.

Steps:

  • Firstly, select cells from B4 to B8.
  • Then, go to the Data tab.
  • After that, choose From Table/Range.

Using Excel Power Query to Remove Blank Characters in Excel

  • After selecting From Table/Range, we get the below window.
  • Next, we tick My table has headers.
  • Then, click OK.

  • Now, we get a table for the query.

  • Therefore, select the Area Code of the table and click the left button of the mouse.
  • So, we will get a Pop-Up.
  • After that, go to the Transform.
  • Besides, select Trim from the options showing.

  • Finally, after selecting Trim, we will get our result.

Using Excel Power Query to Remove Blank Characters

  • From the result, we see that by applying this method, we can only remove leading blank characters. Blank characters in the middle cannot be removed by this method.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Here, we discussed 5 methods to remove blank characters for different cases. Hope this will help you to get your desired solution. Please put your suggestion in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo