Here’s how to remove space in Excel without any hassle. read the article to know about it.
How often does it happen to you that you are searching for particular data in your Excel sheet and have tried every way to find it but have been unsuccessful?
Well, this usually happens when there are some extra spaces before or after any data that you have entered which makes it difficult for you to search for them. This is most common when you copy and paste some information. These errors are easily missed and hence this problem occurs.
Table of Contents
How To Remove Space In Excel
There are many ways that you can avoid this problem, Excel gives several ways to do so and this is how you can do it too.
Use the Trim formula to remove extra spaces
With the help of the TRIM function, all spaces except a single space between words can be eliminated. Let us find out how this works.
1. Add the helper column to the end of your data. You can name it “Trim”.
2. In the first cell of the helper column (C2), enter the formula to trim excess spaces =TRIM(A2)
3. Copy the formula across the other cells in the column. You can use some tips by entering the same formula into all selected cells.
4. Replace the original column with the cleaned data. Select all cells in the helper column and press Ctrl + C to copy data.
Pick the first cell in the original column and press Shift + F10 or the menu button. Then just press V.
5. Remove the helper column and done. You have now deleted all excess blanks with the help of the TRIM formula.
Also Read
How to search in MS Excel in simple steps
How to remove duplicates in Excel
Using A Formula To Remove All Spaces
You may need to delete all blanks, like in a formula chain. You can do this by creating a helper column and entering the formula:
=SUBSTITUTE(A1,” “,””)
Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.
Then follow the steps from the above-mentioned step.
Using Find & Replace to remove extra spaces between words
This allows for deleting only excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed.
1. Select one or several columns with the data to delete spaces between words.
2. Press Ctrl + H for the “Find and Replace” dialogue box.
3. Press the Space bar twice in the Find What field and once in Replace With
4. Click on the “Replace all” button, and then press Ok to close the Excel confirmation dialogue.
5. Repeat step 4 until you see the message “We couldn’t find anything to replace.”
Also Read
How to remove blank rows in Excel
How to remove scroll lock in Excel
Using the Trim Spaces Tool
This is the best option to remove extra spaces in Excel because it clears all unwanted spaces such as excess blanks between words, leading and trailing spaces, non-breaking spaces, non-printing symbols and line breaks and that too in just three clicks.
1. Download and install a trial version of Ultimate Suite for Excel.
2. Select the range in your table, or press CTRL+A to select all cells.
3. Go to the Ablebits Data tab which is next to the Data tab. Click on the Trim Spaces icon.
4. You’ll see the add-in pane open on the left side of your worksheet. Select the needed checkboxes and click the Trim button to have a perfectly cleaned table.
It is an easy job to remove extra spaces in an Excel sheet, especially with the help of the TRIM method. This is great especially when you are working in large sheets with vast data. It will make your work more efficient and error-free.
CONCLUSION
Microsoft Excel is a robust spreadsheet program that comes standard with Microsoft Office and is primarily used to record data in tables. We can add several rows and columns to which we can enter data.
EasySheet is a tech blog where you can find amazing Excel hacks, tips, and tricks. Our goal is to make Excel more accessible to beginners and to provide them with the Easy Sheet as a resource for all of their questions.
We are the best place to go for anyone who needs assistance with Microsoft Office products such as Excel, Word, PowerPoint, Publisher, and others.
