Excel is an immensely sophisticated data analysis tool that, even after more than 30 years, remains the go-to for everyone. Understanding certain Excel functions will help you increase your productivity. These are also highly useful when you wish to remove small flaws such as unnecessary spaces and so on.
When you paste data from an external source (web pages or text reports), you may encounter additional spaces in addition to vital data. Your table will become disorganized and difficult to utilize.
How to Delete Spaces in Excel
In Excel, there are three methods for removing excess spaces. You may utilize the Find & Replace, Trim formula, and Trim spaces tools. Let’s look at how to do this.
Using Find & Replace Option
This is the simplest method for removing spaces in Excel, however it can only be used to erase gaps between words. If there are leading and trailing spaces, they will be reduced to one but not completely erased.
- Choose one or more columns from which to remove excess spaces.
- Select Find and Replace or press CTRL+H.
- In the Find what box, press the space bar twice, and in the Replace with box, press the space bar once.
- To save your changes, click Replace All and then OK.
- Step 4 should be repeated until you get the message “We couldn’t locate anything to replace.”
RELATED: How to Use Excel’s Find and Replace Search Feature
Using the Trim Formula
This is the Excel formula to use if you need to eliminate spaces. Though it takes some time, it is efficient and works best for trailing and leading areas.
- Begin by inserting the Helper column at the end of your data and labeling it “Trim.”
- Enter the Excel formula to eliminate spaces =TRIM in the first cell of the Helper column (A2).
- Repeat the formula in all other cells as needed.
- Now, replace the old column with the clean data column. You may copy all cells in the Helper column by selecting them all and pressing CTRL+C.
- After that, select the first cell in the Helper column, press Shift+F10, and copy it with CTRL+V.
- Remove the Helper column, and any superfluous spaces will be erased.
RELATED: How to Use the TRIM Function in Excel
To eliminate excess spaces in Excel, the Trim Spaces add-in is the best alternative. That’s because it removes all unneeded spaces in three clicks, including leading and trailing spaces, extra blanks between words, non-breaking spaces, line breaks, and non-printing symbols. In most situations, the approaches outlined above only work for gaps between words. Find & Replace is the easiest way to eliminate excess spaces between numbers in Excel. Excel treats a large number of numerals separated by spaces as text.
- To begin, download and install the Ultimate Suite for Excel trial version.
- In your table, choose the range, or use CTRL+A to select all cells.
- Next, go to the Ablebits Data tab, which is located just next to the Data tab. Select the Trim Spaces option.
- The add-window in’s will appear on the left side of your worksheet. To get a totally clean table, tick the necessary options and click the Trim button. Open the Find and Replace dialog box.
How to Remove Spaces Between Numbers
Using the TRIM method might take some time, particularly when working on big spreadsheets. That is why we advise you to use the Trim Spaces tool. It is the most efficient and completes your task in less than three clicks.
- Fill in the Find what field with a single space and leave the Replace with tab blank.
- After that, click Replace All and then OK to confirm your changes. And presto! All unnecessary gaps will be eliminated. Take pleasure in a nicely clean spreadsheet.
- Next, click on Replace All and press OK to confirm your changes. And voilà! All extra spaces will be removed. Enjoy a perfectly clean spreadsheet.
The Find & Replace function should suffice for any of your small requirements. Regardless of the option you choose, deleting excess spaces in Excel is stupidly simple.
For all your minor needs, the Find & Replace option should do more than just fine. No matter which option you choose, removing extra spaces in Excel is ridiculously easy.
You are looking for information, articles, knowledge about the topic How to Get Rid of Spaces in Excel on internet, you do not find the information you need! Here are the best content compiled and compiled by the appsladder.com team, along with other related topics such as: How To.
Related videos about How to Get Rid of Spaces in Excel