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.
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.
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.
- 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 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.
- 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.
- 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.
- 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.
- 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.
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
- How to Remove Dashes from SSN in Excel
- How to Remove Non-Alphanumeric Characters in Excel
- How to Remove Apostrophe in Excel
- How to Remove Asterisk in Excel
- How to Remove Parentheses in Excel
- How to Remove Semicolon in Excel
- How to Remove Dashes in Excel
- How to Remove Dashes from Phone Number in Excel
