Alok Paul
Excel is one of the most widely used applications in our professional life. We organize, calculate, and 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. Here is our overview image of removing blank characters in Excel.
Version
- These functions are available from Microsoft Excel 2007.
- Here, we will use Microsoft Excel 365.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Easy Ways to Remove Blank Characters in Excel
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 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.
Method 1: Using Find and Replace Command 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!
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.
- 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.
Read More: How to Remove Specific Characters in Excel ( 5 Ways)
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.
- 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.
Read More: How to Remove the Last 3 Characters in Excel (4 Formulas)
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 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.
Steps:
- Firstly, choose cell C5.
- Secondly, type the following formula.
=TRIM(CLEAN(B5))
- After that, press Enter.
- Finally, pull the Fill Handle icon from Cell C5 to C8 to get all the results.
Related Content: How to Remove Special Characters in Excel (4 Methods)
Similar Readings:
- How to Remove Hidden Double Quotes in Excel (6 Easy Ways)
- Remove Semicolon in Excel (4 Methods)
- How to Remove Numeric Characters from Cells in Excel (5 Methods)
- Remove Apostrophe in Excel (5 Easy Methods)
- How to Remove Parentheses in Excel (4 Easy Ways)
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 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 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.
- 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.
Read More: How to Remove Characters in Excel (6 Methods)
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.
- 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.
- 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.
Read More: How to Remove the First Character from a String in Excel with VBA
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.
