This tutorial explains how to remove leading, trailing spaces from entire Excel sheet. At the end of this tutorial you will be able to easily remove extra spaces that you forgot to remove while creating the workbook. I will use a free Excel add-in here called Ablebits.com Trim Spaces for Excel. This add-in removes leading, trailing spaces from the cell value and you can also remove multiple spaces from the middle of a cell value and replace with a single space. And you can do it with just a single click. The Ablebits.com Trim Spaces for Excel add-in is fully compatible with MS Excel 2013 or later, Excel 2016 for MAC, and, Excel Online.
Sometimes your Excel sheet might have data with extra spaces. The spaces could be in beginning or end, or multiple spaces in between. After going through this tutorial, you will learn how to remove all of them in a single click.
Let’s see, how to remove leading, trailing spaces from entire Excel sheet.
How to Remove Leading, Trailing Spaces from Entire Excel Sheet?
Ablebits.com Trim Spaces for Excel is fee Add-in for Excel that can remove leading, trailing spaces from entire Excel sheet. Not only leading, trailing spaces, you can also remove non-breaking spaces from your sheet. And the entire process finishes in just a few clicks.
To get started with Ablebits.com Trim Spaces for Excel, follow these simple steps.
Step 1: Download and add Ablebits.com Trim Spaces for Excel add-in from here. You can add it using the Excel as well. Just navigate to the Insert tab and open Office store under Apps for Office drop down. And then search for this add-on there.
Step 2: Now, open any worksheet in Excel and select all the cells that contain unnecessary spaces.
Step 3: Invoke the add-in by navigating to the Apps for office drop down. You will see its interface similar to the following screenshot.
Step 4: It shows 3 options to remove spaces from the sheet:
- Time leading / trailing spaces: This option will remove all leading / trailing spaces from your cells. Its similar to trim function of Excel.
- Trim spaces between words to 1: This is my favorite feature of this add-on. If the words in your cells have multiple spaces, then use this option to replace those multiple spaces by a single space. Its very hard to do the same otherwise.
- Trim ‘ ’: This option simply removes the non-breaking spaces from Excel.
Choose the options that you want.
Step 5: Now, select some or all the cells from which you want to remove spaces, and hit the Trim Spaces button. A dialog will appear, saying that if any cells contain a formula, then it will be overwritten by the values. To proceed, click on the Yes button and you will see the all the spaces are now removed from the sheet.
So in this way, you can remove all unnecessary spaces from any Excel sheets in just a few clicks. Ablebits.com Trim Spaces for Excel makes it very easy to do that. I really like the fact that using this add-in, you can easily remove non-breaking spaces as well.
Only limitation of this method is that if there are some cells that have formulas, then they will be replaced by Values.
Also see:
My Final Verdict
In the tutorial above, I have demonstrated how to remove leading, trailing spaces from entire Excel sheet. And Ablebits.com Trim Spaces for Excel add-in does this in just a few clicks. Using this free add-in, you can remove spaces from initial, last, and middle of a cell value. So, if you usually end up having some unnecessary spaces in your Excel sheets, then this tutorial will be a great help to you. The add-in mentioned in the tutorial will save you both time and energy.
