This tutorial is about How to Remove Spaces in MS Excel. Recently I updated this tutorial and will try my best so that you understand this guide. I hope you guys like this blog, How to Remove Spaces in MS Excel. If your answer is yes after reading the article, please share this article with your friends and family to support us.
Check How to Remove Spaces in MS Excel
You will likely notice a lot of unnecessary spaces when importing data from external sources and web pages. Here’s how you can get rid of them. Excel is an immensely sophisticated data analysis tool that, even after more than 30 years, is still the ideal choice for everyone. It is a good idea to learn some Excel functions so that you can increase your productivity. These are also very useful for troubleshooting small issues like removing excess gaps and the like.
When pasting data from an external source (web pages or text reports), you may notice unnecessary gaps appear along with critical data. Your table is disorganized and uncomfortable to use.
How to remove spaces in Excel
There are three ways to remove extra spaces in Excel. You can use the Find and Replace option, the Crop formula, and the Crop Gaps tool. Let’s take a look at how to do it.
Using the Find and Replace option
This is the easiest way to remove spaces in Excel, but you can only use it to remove spaces between words. If there are leading and trailing spaces, they will be reduced to 1, but not completely removed.
- Select one or more columns from which you would like to remove extra spaces.
- Click on Search for and replace or press CTRL + H.
- Press the space bar twice on the Find what box, and press the space bar once in the Replace with box.
- Click on Replace everything and press okay to save your changes.
- Repeat step 4 until you see the message “We couldn’t find anything to replace.”
Using the clipping formula
If you’re looking for an Excel formula to remove spaces, this is the one. Although it takes a bit of time, it is efficient and works best for leading and trailing spaces.
- Start by adding the Auxiliary column at the end of your data and name it “Trim”.
- In the first cell of the Helper column, enter the Excel formula to remove spaces = CUT (A2).
- Copy the formula into all other cells as needed.
- Now replace the original column with the one with the clean data. You can select all cells in Helper column, press CTRL + C to copy them to the clipboard.
- Once done, choose the first cell in the Helper column, press Shift + F10, and paste the same by pressing CTRL + V.
- Delete the Helper column and you have removed all the extra spaces.
Using the Crop Gaps tool
The Trim Spaces add-in is the best option to remove extra spaces in Excel. This is because it erases all unwanted spaces including leading and trailing spaces, excess whitespace between words, non-breaking spaces, line breaks, symbols not printing, and it does so with three clicks.
- To get started, download and install a trial version of Ultimate Suite for Excel.
- Select the range in your table, or press CTRL + A to select all cells.
- Then go to Ablebits data tab which is right next to the Data tab. Click on the Crop spaces icon.
- You will see the plugin panel open on the left side of your worksheet. Select the required check boxes and click the Crop button 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.
- Open the Find and Replace dialog box.
- Write a single space in the Find what field and keep the Replace with empty tab.
- Then click Replace everything and press OK to confirm your changes. And voila! All extra spaces will be removed. Enjoy a perfectly clean spreadsheet.
Final remarks: How to Remove Spaces in MS Excel
I hope you understand this article, How to Remove Spaces in MS Excel. If your answer is no, you can ask anything via the contact forum section related to this article. And if your answer is yes, please share this article with your friends and family to give us your support.
