5 Ways to Remove Blank Characters in Excel

Apparently, you may not see the blank spaces with the bare eye. But blank characters, such as spaces, tabs, or line breaks, can manipulate the data. Blank spaces in a dataset can cause inconsistencies and errors, especially if you’re performing calculations or analysis. For this reason, removing the blank characters is sometimes necessary. Excel offers several ways to remove these blank characters that can make data analysis easier. In this article, I will show you five different methods that you can use to remove blank characters in Excel.

download remove blank characters in excel.xlsx

Table of Contents

Introduction to the Dataset

To show you how to remove blank characters in Excel, I have taken a list of email addresses in my dataset. The email addresses are basically copied from a text file. For this reason, the email addresses are not arranged properly in the worksheet. I have created another column here named No Space (column B) to show you the results.

Easiest Way to Remove Blank Characters in Excel

The SUBSTITUTE function allows you to use alternate characters in place of other characters. It is a very reliable way to remove blank spaces in Excel sheets.

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Formula

=SUBSTITUTE(A2,” “,””)

Usage Guide

Step_1: Write the formula in cell B2.

Step_2: Hit the ENTER key.

Step_3: After that, place the cursor on the bottom-right corner of cell B2.

The Fill Handle icon will appear.

Step_4: Now double-click on the Fill Handle icon.

Final Result

There were multiple blank characters in most of the cells of column A. The black spaces were either at the beginning, middle, or end of the test string. After using the SUBSTITUTE function, you can see that there are no blank characters left in the text strings displayed in column B.

Alternative Way #1: Use the Find and Replace Feature to Remove Blank Characters in Excel

There exist certain formulas that facilitate the removal of blank characters. But there is a different method that allows the removal to be done without the need for any formula. It is using Excel’s Find and Replace feature. It can easily remove any designated character. I am going to use the Find and Replace feature to remove the blank characters in Excel.

Usage Guide

Step_1: First, choose the range of cells from A2 to A10.

Step_2: Secondly, go to the Home tab.

The Find and Select drop-down menu is located in the Editing group of the Home tab.

Step_3: Now right-click on the Find and Select drop-down menu.

Step_4: After that, select the option named Replace from the drop-down menu.

You will see the Find and Replace dialog box will pop up in the Excel sheet.

Step_5: Then open the Replace tab.

Step_6: Now inside the Find What box, type a space “ ”.

Step_7: Skip the Replace With box.

I did not enter any characters in the Replace With box because I don’t want anything replacing the space “ ”. However, you can mention anything you want in the Replace With box, and you will get your desired result.

Step_8: Now hit the Replace All button.

Step_9: Finally, click on the Close button.

You will see a window with this message.

Step_10: Hit the OK button.

Final Result

The Find and Replace feature is a great tool to remove the unwanted blank characters present in the Excel sheet.

3+ Ways to Remove Dashes in Excel

Alternative Way #2: Use the Power Query to Remove Blank Characters in Excel

Blank spaces can also affect how your data is processed and analyzed. For example, if you’re performing a pivot table analysis, blank spaces can interfere with the grouping and sorting of data. By removing blank spaces, you can ensure that your data is properly processed and analyzed.

Note: This approach only eliminates spaces at the start and end of words. But it does not remove any spaces between them.

Usage Guide

Step_1: Begin with selecting the cell range A2:A10.

Step_2: Then go to the Data tab.

Step_3: Now, from the Get and Transform group, select the From Table command.

A window named Create Table will pop up.

Step_4: Check the cell range in the window.

Note:

If the data range mentioned in the Create Table window is not correct, you can adjust it again.

In my dataset, I have table headers. For this reason, I put a tick mark on My Table Has Headers. If your table does not have headers, you can leave the checkbox empty.

Step_5: Click on the OK button.

Now the Power Query Editor will appear on the screen.

Step_6: Take the cursor to the column header and right-click on the column header.

Note: Do not right-click on the small drop-down icon on the column header. You have to click on the body of the column header.

Step_7: From the menu, take the cursor to Transform and select Trim.

You will notice the extra spaces are no longer present.

Step_8: Click on the Close and Load command to close the Power Query Editor.

Final Result

You will find the Power Query‘s results are now sent to a separate Excel sheet rather than the original Excel file.

Alternative Way #3: Use the TRIM Function to Remove Blank Characters in Excel

Another way to chop out irregular spacing is by using the TRIM function in Excel.

Note: The TRIM function can only eliminate the starting and ending blank characters from the cells.

Syntax

=TRIM(text)

Formula

=TRIM(A2)

Usage Guide

Step_1: Take a spare column in the worksheet to perform the formula.

I took column B and gave it the column header Result.

Step_2: Write the formula in cell B2.

Step_3: Press ENTER to apply the formula.

Step_4: Now double-click on the Fill Handle icon located at the bottom-right corner of cell B2.

The Fill Handle icon will copy the formula into the entire cell range B2:B10.

Final Result

The TRIM function has trimmed out all the extra spaces, and the results are displayed in column B.

Alternative Way #4: Use VBA to Remove Blank Characters in Excel

VBA (Visual Basic for Applications) is a programming language that is integrated with Microsoft Excel. It is a really helpful tool that can customize the functionality of Excel. In this method, I am going to use the VBA codes to instruct it to remove blank characters from the worksheet.

This piece of VBA code can count all the columns having data in an Excel sheet. Just insert the code inside a new Module in your Visual Basic Editor and Run it.

Sub RemoveBlankCharacters()

Dim gRng As Range

For Each gRng In Selection.Cells
gRng.Value = VBA.Trim(gRng.Value)
Next gRng

End Sub

Usage Guide

Step_1: At first, go to the Developer tab.

Step_2: Now click on the Visual Basic command from the Code group to open VBA.

Shortcut: Press Alt+F11 or Alt+Fn+F11 to open VBA.

The VBA window comes in front of the Excel sheet.

Step_3: Now take the cursor to the project panel and right-click on your current sheet name.

The sheet name that I am working with in my workbook is VBA.

Step_4: From the drop-down menu, take the cursor to Insert then select Module.

It will add a Module to your sheet. Here, Module2 is added to my sheet.

Step_5: Insert the VBA code now.

Step_6: After that, click on the top-right cross icon to close the VBA window.

You will return to the Excel sheet again.

Step_7: Then select the cell range A2:A10.

Step_8: Now, under the Developer tab, click on the Macros icon.

Shortcut:

Use Alt+F8 or Alt+Fn+F8 to open Macros.

Step_9: From the Macro dialog box, hit the Run button.

Final Result

This way you can remove the black characters with the help of the VBA in Excel.

Conclusion

I’ve discussed five very handy techniques that you can use to remove blank characters from an Excel sheet. You can find more articles about removing characters in Excel on our website. Click here to explore more about Excel. You are invited to drop your valuable opinion in the comment section below. Have a nice day!

Related Articles