This sample dataset contains cells with multiple blank spaces.
Method 1 – Using the TRIM Function to Remove Blank Spaces
Enter the following formula in an empty cell (A16).
=TRIM(A5)The TRIM function will remove extra blank spaces from A5.
Press ENTER and no blank spaces will be displayed in A16.
Drag A16 to apply the formula to all other cells in column A.
Read More: How to Remove Space Before Text in Excel
Method 2 – Using the Find and Replace Command
Go to Home> Editing > Find & Select > Replace
In the Find and Replace window, enter single space in Find what box and click Replace All.
Click OK in the confirmation box and close the Find and Replace window.
All blank spaces have been removed.
Method 3- Using the SUBSTITUTE Function to Remove Blank Spaces
You can also use the SUBSTITUTE function to remove blank spaces. Enter the following formula in B16.
=SUBSTITUTE(B5, " ", "")The substitute function will remove the spaces in B5.
Press ENTER and the text will be displayed without blank spaces in B16.
Drag B16 to apply the formula to all other cells in column B.
Method 4 – Remove a Blank Space using the REPLACE Function
The REPLACE function can also remove blank spaces. Enter the following formula in B16.
=REPLACE(B5,1,LEN(B5)-LEN(TRIM(B5)),"")The LEN function indicates the length of the string in B5. LEN(B5)-LEN(TRIM(B5) gives the number of blank spaces. The REPLACE function replaces the original text with the string without blank spaces.
Press ENTER to see the text without blank spaces in B16.
Drag B16 to apply the formula to all other cells in column B.
Method 5 – Using the Text to Column Feature to Remove Blank Spaces
Select the column and go to Data> Data Tools > Text to Columns
In the Convert Text to Columns Wizard window, select Fixed width and click Next.
Move the vertical line to the end of your text and click Next.
Select General and click Finish.
Cells in the selected columns have no blank spaces.
Method 6 – Using a VBA to Remove Blank Spaces
- Press ALT+F11 to open the VBA window.
- In Project, right-click the sheet name.
- Choose Insert from the dropdown menu and select Module.
A Module(Code) window will be displayed.
Enter the following code in the Module.
Sub Remove_Blanks()
Dim x As Range
For Each x In Selection.Cells
x.Value = VBA.Trim(x.Value)
Next x
End Sub
Close the VBA window. Select your dataset and go to View > Macros.
A Macro window will open. Click Run.
All blank spaces will be removed.
Method 7 – Using the Power Query to Remove Blank Spaces
Select your dataset and go to Data > Get Data > From Other Sources > From Table/Range
A Create Table dialog box will open. Click OK.
The Power Query Editor window will open.
All your data is displayed.
Right-click any header and select Transform > Trim.
Repeat the same procedure for all the columns. Blank spaces will be removed.
In the home tab, select Close & Load.
Data is will be displayed in a new sheet: Table.
Download Practice Workbook
Related Articles
- How to Remove Extra Spaces in Excel
- How to Remove Leading Spaces in Excel
- How to Remove the Trailing Spaces in Excel
- How to Find and Replace Space in Excel
- How to Remove Space after Number in Excel
- How to Remove Space in Excel Before Numbers
- How to Remove Space in Excel after Text
- How to Remove Space between Rows in Excel
- How to Remove Tab Space from Excel
- How to Remove White Space in Excel
<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
