Even after 30+ years since its invention, Excel has continued to be the most popular tool for data analysis. Excel functions can help you improve your productivity, so mastering some of them is a good idea. Additionally, they are beneficial when you want to get rid of minor glitches, such as how to remove spaces from Excel.
When you copy/paste data from an external source (text reports or web pages), the data often gets truncated. The table becomes disorganized and hard to use.
Table of Contents:
How to Remove Spaces from Excel
You can use Excel’s TRIM function to remove leading, trailing, and extra spaces between words in a string. TRIM removes all spaces between words except for single spaces.
Using the Replace feature in Excel, you can eliminate all spaces, even between words. Alternatively, you can limit the range to a specific area within the Excel spreadsheet.
Five methods are available for removing extra spaces from Excel. Besides using the Find & Replace option, you can also use the Trim formula, power query, and Trim spaces. Here is how to do it.
1: Find & Replace
There is an easier way to remove spaces in Excel, but you can only use it to remove spaces between words. There will be one leading and trailing space, but they won’t be removed entirely.
- You can remove extra spaces from one or more columns.
- Press CTRL+H to find and replace.
- To find what, press the spacebar twice, and to replace with, press the spacebar once.
- To save your changes, click Replace All and then click OK.
2: Trimming Formula
It is a formula you can use to remove spaces in Excel. Even though it’s time-consuming, it works great for trailing and leading spaces.
- Create a column called “Trim” at the end of your data.
- The Excel formula to remove spaces is =TRIM (A2). Enter this formula in the first cell of the Helper column.
- All other cells should be copied with the formula.
- Put the data that has clean data in place of the original column. Then press CTRL+C to copy all cells in the Helper column.
- To paste the Helper column, select the first cell and press Shift+F10, then press CTRL+V.
- If you remove the Helper column, all extra spaces will be gone.
3: Power Query
A stray blank space can also cause queries to fail, as with functions and formulas. You can check the Editor’s preview to see if any blank spaces are causing the problem if you are unsure.
Power Query has a Trim feature on the right-click menu to help you know How to Remove Spaces in Excel after text. Choose Transform and Trim from the right-click menu when you have selected the column you wish to fix. The query will return the accurate values if this trims all the white space before and after the text string.
4: Using a Formula to Remove Space in Excel
In a formula chain, you may need to remove all blanks. The following formula can be entered into a helper column: =SUBSTITUTE(A1,”, “”)
- It is essential to remove all spaces from A1 in the column of numbers and words.
- To remove extra space in excel between words, follow the steps in the section using a formula.
- Add a temporary helper column at the end of the spreadsheet for the corrected data. This column will be called No Spaces.
- In the Trim column, click the first cell. Select SUBSTITUTE from the dropdown menu on the Formulas tab of the Functions group.
Upon the appearance of the dialogue box:
- To change a cell in a column, click on the first cell.
- Put the old text in the “Old_text” field and separate it with quotes. Remove spaces by using”. “
- Enter your desired character in the “New_text” field. Since a space should not be replaced with any characters, type “. “
- By clicking OK, the formula will be applied to the cell.
- Fill in the rest of the helper columns with the formula. You can paste the data into the data column using CTRL-C and Paste Options.
5: Macros and VBA
Using the same example as the TRIM function, let’s trim extra spaces from text using a macro.
- VB Editor should be opened first, then press the Visual Basic button on the Developer tab, and you can also use shortcut keys Alt + F11.
- Here is a loop through each cell in a simple macro and replace the value with the trimmed value. Download the macro at the top of this post if you’d like to copy it. You can assign macros to buttons, one of the benefits of using them. It’s already done here on a worksheet button.
- You can add it to the ribbon: then put the macro in your Personal Macro Workbook, and give it a button. So now you can use this macro on any workbook.
- The downside of using the macro method is you can’t undo the action once the macro runs. If you think you might have to untrim the data for some reason, it might be a good thing to save the file before running the macro.
Furthermore, the VBA Trim function does not remove extra blank spaces between words. TRIM in Excel will remove those spaces. The TRIM program will remove the additional space between a first and last name if two spaces exist between them. It is not possible with VBA’s Trim tool. Only the end or beginning of the string is removed.
Wrapping up
Now you may have to get your answer from the above article on how to remove spaces from excel sheets. You can consider any method to remove extra spaces from the downloaded documents to make it clear data.
