Table of Contents
This tip is about the how to Remove Spaces in MS Excel. So read this free guide, How to Remove Spaces in MS Excel step by step. If you have query related to same article you may contact us.
How to Remove Spaces in MS Excel – Guide
You will likely notice a lot of unnecessary spaces when importing data from external sources and web pages. See How to get rid of them. Excel is an extremely sophisticated data analysis tool that, even after more than 30 years, is still the right choice for everyone. It’s a good idea to learn some Excel functions to increase your productivity. They are also very useful for fixing small problems like deleting excess spaces and the like.
When pasting data from an external source (web pages or text reports), you may notice that unnecessary gaps appear next to critical data. Your desk is disorganized and inconvenient to use.
How to Exclude spaces in Excel
There are three ways to remove extra spaces in Excel. You can use the Find and Replace option, the Trim formula, and the Trim Spaces tool. Let’s take a look at how to Knife.
Using the Find and Replace option
This is the easiest way to remove spaces in Excel, but you can only use it to delete spaces between words. If there are spaces to the left and right, they will be reduced to 1 but not completely removed.
- Select one or several columns from which you want to exclude extra spaces.
- Click Find and Replace or press CTRL + H.
- Press the spacebar twice in the Find box and press the spacebar once in the Replace with box.
- Click Replace All and press OK to save your changes.
- Repeat step 4 until you see the message “Could not find anything to replace”.
Using the Cutting Formula
If you’re looking for an Excel formula for removing spaces, this is it. Although it takes a little time, it is efficient and works best for spaces to the right and to the right.
- Start by adding the Helper column to the final of your data and call it “Trim”.
- In the first cell of the Helper column, enter the Excel formula to remove spaces = TRIM (A2).
- Copy the formula into all other cells as needed.
- Now replace the original column with the one that contains clean data. You can select all cells in the Helper column, press CTRL+C to copy them to the clipboard.
- Once this is done, choose the first cell in the Helper column, press Shift + F10 and paste it by pressing CTRL + V.
- Remove the Helper column and you will have removed all extra spaces.
Using the Trim Spaces tool
The Trim Spaces add-in is the best choice for removing extra spaces in Excel. That’s because it cleans up all unwanted spaces, including leading and trailing spaces, excess white space between words, non-breaking spaces, line breaks, non-printing symbols, and it does that in three clicks.
- You will see the add-in panel open on the left side of your spreadsheet. Select the required checkboxes and click the Trim button button to have a perfectly clean table.
How to Remove Spaces Between Numbers
The methods mentioned above only work for spaces between words in most cases. To remove extra spaces in Excel between numbers, Find and Replace works better again. When you have many digits separated by spaces, Excel sees them as text.
Final note
I hope you like the guide How to Remove Spaces in MS Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.