Nazmus Sakib
Blank characters or spaces can be a major obstacle when dealing with a large dataset. If we import data from different sources or multiple persons use a workbook, blank characters can appear. In this article, we will show you how to remove blank characters with 6 approaches in Excel.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Remove Blank Characters in Excel with These 6 Easy Approaches
Today we will learn how to eliminate blank characters using the following sample dataset.
1. Using TRIM Function
In our first approach, we will use the TRIM function to remove blank characters from our dataset.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell G6 and type in the following formula, and press Enter:
=TRIM(B6)
- Next, move the cursor to the bottom right corner of cell G6 and drag the Fill Handle icon to cell I6.
- After that, use the Fill Handle icon again from the bottom right corner of cell I6 and double-click.
- Done! As a matter of fact, all blank characters are now removed from the dataset.
2. Merging TRIM, CLEAN, and SUBSTITUTE Functions
In our second approach, we will coalesce TRIM, CLEAN, SUBSTITUTE, and CHAR functions to remove blank characters.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell G6 and Enter the formula below:
=TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160),””)))
- Afterward, use the Fill Handle icon just like the previous approach.
- As a result, all the superfluous spaces are now eliminated from our dataset. You’re all set!
🔨 Formula Breakdown
TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160),””)))
👉 The SUBSTITUTE function will substitute the hidden spaces with an empty string in cell B6. Here, the CHAR function returns the space character.
👉 The CLEAN function removes all the non-printable characters.
👉 The TRIM function removes all the spaces excluding the single spaces between words
📕 Read More: 5 Ways to Remove Last Character from String in Excel
3. Implementing Find and Replace Feature
Now, we will demonstrate how to eliminate blank characters using the Find and Replace dialog box. Let’s start!
⬇️⬇️ STEPS ⬇️⬇️
- First, navigate to the Home tab and click Find & Select.
- Later, click Replace.
- The Find and Replace dialog box will appear on the screen momentarily. Or you can just press Ctrl+F concurrently to open the Find and Replace dialog box.
- Move to the Replace. Now, put a space in the Find what box and leave the Replace with box empty. Click Replace All.
- A tiny dialog box will show how many replacements were made. Click OK and Close the Find and Replace dialog box.
- Voila! Consequently, the Find and Replace dialog box removed all the spaces.
Note:
Using the Find and Replace dialog box will remove all the spaces including the spaces between the two words.
4. Applying REPLACE Function
Now, we will show how to apply REPLACE and LEN functions to remove blank characters.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G6 and Enter the formula below:
=REPLACE(B6,1,LEN(B6)-LEN(TRIM(B6)),””)
- Next, use Fill Handle icon just like in the first approach to apply the formula.
- Great! Subsequently, we have removed the blank characters.
Note:
The REPLACE function will only remove the leading blank characters.
🔨 Formula Breakdown
REPLACE(C6,1,LEN(C6)-LEN(TRIM(C6)),””)
👉 The REPLACE function will take cell B6 as the old text. It will start from the first character of the old text.
👉 The LEN function will calculate the number of characters in cell B6. The TRIM function will trim the unnecessary spaces and the LEN function will count the characters. We subtracted them and used the result as the number of characters in the old text that you want to replace.
👉 Finally, we will replace the spaces with an empty string.
📕 Read More: 3 Ways to Remove Non-Numeric Characters from Cells in Excel
5. Executing VBA Code
Now it is time to learn how to execute a VBA code to eliminate blank characters from our dataset.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select cells B5:E11. Then, navigate to the Developer tab and click Visual Basic.
- In the new window, click Module from the Insert tab.
- A code window will open. Paste the following code in the window.
Sub Remove_Blank_Characters()
Dim rg As Range
For Each rg In Selection.Cells
rg.Value = VBA.Trim(rg.Value)
Next rg
End Sub
- Now, go back to the VBA worksheet. Navigate to the Developer tab to click Macros.
- Instantly, a Macro window will pop up. Click Run.
- Done! You’re all set!
Note:
This code will only remove the leading blank characters.
6. Utilizing Power Query
In our final approach, we will use Power Query to remove blank characters.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cells B5:E11. select Data tab and click Get Data drop-down menu. From Other Sources, click From Table/Range.
- Immediately, a Create Table dialog box will open. Click OK.
- Then, the Power Query Editor will appear.
- Later, right-click on the Items header. From Transform, click Trim.
- This will remove the leading blank characters from the first column briefly.
- Eventually, do the same for the other two columns.
- Afterward, From the Home tab, click Close & Load.
- Furthermore, the table will load in a new worksheet.
Note:
The Power Query will only remove the leading blank characters.
📝 Takeaways from This Article
📌 First, we learned how to remove blank characters using the TRIM function.
📌 Secondly, we demonstrated to remove blank characters from the dataset by combining TRIM, CLEAN, and SUBSTITUTE functions.
📌 Thirdly, we implemented Find and Replace dialog box to remove all the spaces.
📌 Afterward, we removed the leading blank characters using the REPLACE function along with TRIM and LEN functions
📌 Later, we embedded a VBA code to remove the leading blank characters.
📌 Finally, we utilized the Power Query to remove the leading spaces.
Conclusion
That concludes the discussion for today. These are some convenient methods to remove blank characters in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
Related Articles
- 7 Quick Tricks to Remove Apostrophe in Excel
- 4 Ways to Remove Last 3 Characters in Excel
- 3 Ways to Remove Non-Alphanumeric Characters in Excel
