How to Remove Blank Characters in Excel (5 Methods)

Excel is one of the most widely used applications in our professional life. We organize, calculate, manipulate our data using this 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.

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. And we have to delete those spaces to retrieve all texts to a proper format.

Data set to remove blank characters in Excel


Download Practice Workbook

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


5 Methods to Remove Blank Characters in Excel

1. ‘Find and Replace’ Tool to Remove Blank Characters

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

Step 1:

  • First, select the cells from which data we want to remove the blank spaces.

‘Find and Replace’ Tool to Remove Blank Characters

Step 2:

  • Now, go to Home.
  • Select Editing from the group of commands.
  • From the drop-down select Find & Select.
  • From the next drop-down select Replace.
  • Or we can press Ctrl + H together to open the window.

‘Find and Replace’ Tool to Remove Blank Characters

Step 3:

  • Now, we get a dialog box named Find and Replace. Put a space in the Find what.
  • Replace what box is kept empty.
  • Then press Replace All.

‘Find and Replace’ Tool to Remove Blank Characters

Step 4:

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

‘Find and Replace’ Tool to Remove Blank Characters

Step 5:

  • Press Close on the Find and Replace.

‘Find and Replace’ Tool to Remove Blank Characters

Step 6:

  • Return to your original dataset in the worksheet. And we can see that no blank characters exist anymore in the data table.

By applying this method, we cannot put space in the required places. It removes all the blank spaces.


2. Use Excel TRIM Function to Remove Blank Characters

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.

Step 1:

  • Go to Cell C5.
  • Now, type the TRIM function.
  • Select B5 in the argument section. So, the formula becomes:
=TRIM(B5)

Excel TRIM Function to Remove Blank Characters

Step 2:

  • Then press ENTER.

Excel TRIM Function to Remove Blank Characters

Step 3:

  • Now, pull down the Fill Handle icon from Cell C5 to C8.

Here, we get the result for the rest of the cells. This function is useful when there is no line break or non-printable letters.


3. Combine TRIM with CLEAN Function 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.

Now, we modify the data and add some line breaks and non-printable characters. Use Alt+ENTER to add line breaks. We add CHAR(7) as the non-printable character. We also add a column to count the LENTH. So, the data will look like this:

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 1:

  • First, apply the TRIM function like the previous method for all the data.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 2:

  • We see that the non-printable word is not removed using the TRIM function. Now, get the row height of each cell of Column C using the LEN function.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 3:

  • Some extra blank characters exist after due to line break.
  • Now, apply the CLEAN function by replacing the TRIM So, the formula becomes:
=CLEAN(B5)

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 4:

  • Press ENTER to get the result.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 5:

  • Now, pull down the Fill Handle icon from Cell C5 to C8.

Step 6:

  • Again, check the length of the cells of Column C.

Step 7:

  • After applying both methods separately we see that some blank characters still exist. But we need to remove all the blank characters.
  • So, combine the CLEAN function with TRIM. And the formula becomes:
=TRIM(CLEAN(B5))

Step 8:

  • Now, press ENTER to get the result.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 9:

  • Now, pull down the Fill Handle icon from Cell C5 to C8.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Step 10:

  • Find out the length as done before.

Combine TRIM with CLEAN Function to Remove Blank Characters in Excel

Now, comparing TRIM, CELAN, and combination of CLEAN with TRIM we can see that the length is minimum in the case of CLEAN &TRIM combination. So, when we have a line break we should use a combination of CLEAN & TRIM combination. Also, without the CLEAN function, we cannot non-printable characters.


4. Add SUBSTITUTE with TRIM Function to remove Blank Characters

From the previous method, we’ve learned how to remove the non-printable character 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 on the following image.

SUBSTITUTE with TRIM Function to remove Blank Characters

Step 1:

  • Go to the Cell C5.
  • Write TRIM, CLEAN, SUBSTITUTE functions.
  • Then, select B5 as the 1st argument.
  • Write CHAR(160) as old text and new text as “”. So, the formula becomes:
=TRIM(CLEAN(SUBSTITUTE(B5,CHAR(160),"")))

SUBSTITUTE with TRIM Function to remove Blank Characters

Step 2:

  • Press ENTER button.

SUBSTITUTE with TRIM Function to remove Blank Characters

Step 3:

  • Pull down the Fill Handle icon to C8.

SUBSTITUTE with TRIM Function to remove Blank Characters

Step 4:

  • Find out the length as done before.

From the result, we see that non-breaking spaces are removed. We used CHAR(160), as the non-breaking space ASCII code is 160.


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.

Step 1:

  • First, select the cells B4 to B8.
  • Then go to the Home.

Excel Power Query to Remove Blank Characters

Step 2:

  • Now, go to the Data tab.
  • Select From Table/Range.

Step 3:

  • After selecting From Table/Range we get the below window.
  • We tick My table has headers.

Excel Power Query to Remove Blank Characters

Step 4:

  • Now, we get a table for the query.

Step 5:

  • Select the Area Code of the table and click the left button of the mouse.

Step 6:

  • We will get a Pop-Up.
  • Go to the Transform.
  • Then select Trim from the options showing.

Excel Power Query to Remove Blank Characters

Step 7:

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

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.


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 Links

How to Remove Space In Excel Before Numbers (3 Ways)

How to Remove the Trailing Spaces in Excel (2 Easy Ways)

Find And Replace Multiple Values in Excel (6 Quick Methods)

How to Find a Character in String in Excel

Alok

Hello, this is Alok. I am a Telecom Engineer. I completed my study at East West University. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo