Microsoft Excel is one of the most used softwares in the corporate world. Excel can help create sheets that can be used for managing data.
With sheets, you can manage all your bills and customer information and the information of the workforce. But what about having the data from some external source?
With most external sources, the problem is that they provide data in an uneven representation, i.e., they will not arrange the data properly.
When taking data from an external source, many people suffer from a misalignment of data. One of the common mistakes that can be found with alignment is the use of spaces. Correctly using spaces helps give a better format to the document.
With even spaces, it becomes easy to find data in a row. When uneven order of spaces is used with the document’s formatting, it becomes difficult to find any particular set of data.
Even when using the shortcut keys, you will be required to enter the data you want to find, and in that spacing does matter a lot.
When the formatting is done with uneven spaces, it becomes difficult to find data even with keyboard shortcuts. So how can one remove the spaces in excel? Let's discuss some methods to help you evenly remove all the unwanted space in an Excel document.
Trimming blanks between words
Trimming the blanks is one of the best ways to remove the spaces between the words. With this method, one can easily remove trailing and leading spaces in between the words. You should not prefer using this method if your sheet is too long and consists of many data.
This process is manual, and you will have to manually enter the trim formula in front of every cell for which you want to trim blanks. So if your document is too long, then you should not prefer using this method. For small documents which do not have much data, this method can be efficient.
Using the Find and replace method
This is one of the fastest and efficient methods to remove spaces. No matter what size of the document you have, you can remove all the uneven spaces at an instance with this method.
To use this method, press CTRL+H on your keyboard and this will open the find and replace dialog box on your screen. Now on the find box press spacebar twice and click on replace all. By doing so, all the unnecessary spaces from excel will be removed.
Using the Formula to remove spaces
You can remove spaces from the document in one instance by using the substitute formula. Suppose column A of the sheet is the one with wrong and unevenly spaces. You can use the formula =SUBSTITUTE(A1,", "" ") to clear the spaces. The column name will replace the letter A.
Removing non-breaking spaces
Non-breaking spaces can be a headache even if, after clearing all the unwanted spaces, some of these are left. Even if, after using the trim function, non-breaking spaces are not removed, you might have to use a different function, which will help remove all the non-breaking spaces.
For this, you will have to use substitute function with this one, and substitute all the non-breaking spaces with the regular spaces.
The very first step when using this method is to know the 7-bit ASCII code for non-breaking space. The character value of non-breaking space is 160, so you will have to use the code char(160). You will then have to convert the non-breaking space to the regular space.
The converted space will then be removed with the help of the trim function.
The function for this is =TRIM(SUBSTITUTE(A2, CHAR(160 ),"")) where A2 is the name of the cell.
Remove ALL Unwanted Spaces in Excel (TRIM and All Its Functions)
Removing the non-printing character
It does sometimes happen that even after you have used the trim and clean function, MS excel is unable to remove the non-printing character and non-breaking spaces.
Non-printing characters are those characters whose ASCII code is not between 0 to 32, and non-breaking spaces are the characters with ASCII code 160. The problem with the non-printing character is that one cannot be sure of what the code is.
For this reason, first, you will have to identify the code of the non-printing character.
You will have to use the CODE function to identify what the code of the function is. With this function, you will first have to identify the non-printing character's code and then use the SUBSTITUTE method to replace the non-printing character and then use the TRIM and CLEAN function to remove it.
- 1You will have to use three columns to use this method. The first column will be used for all the data that has to be arranged by the function, the second column will be used for identifying the code, and the third will be used for using the main function.
- 2There are three codes to find the non-printing character: =CODE(LEFT(A2,1))
With this code, you will be able to select the leading spaces or non-printing characters at the beginning of the string: =CODE(RIGHT(A2,1)) With this code's help, you will be able to select the ending spaces or non-printing characters at the end of the string: =CODE(MID(A2,4,1))
With this code, you will be able to select the ending spaces or non-printing characters present in between the strings. Now that you have found the code for the non-printing characters, it's time to head to the next step.
Now you will have to substitute the non-printing character with regular space and use the TRIM function: =TRIM(SUBSTITUTE(A2, CHAR(127), " "))
The number 127 is the ASCII code for the non-printing character, and A2 is the cell name. SUBSTITUTE function is used for substituting the non-printing character with regular spaces, and TRIM is used to remove regular spaces.
Use Add-on to clear spaces
You can also add add-ons to your excel software, which can be used to clear the spaces with a clean pattern. You can use the Ablebits cell cleaner add-on to clear spaces. With this method, you won't have to go through all the steps above and remember all the formulas.
This method is simple enough and one of the most efficient methods to help clear spaces as it helps to clear spaces at an instance. Even if you have a high amount of data that is to be converted into a well-arranged pattern, you can use it.
This not only helps you work at an instance but also saves a lot of time.
- 1Once you have downloaded the add-on and installed it, it's time to make changes to the document.
- 2Select all the data to which the changes are to be made. Now after successfully selecting all the data, click on Ablebits data from the toolbar.
- 3After clicking on the toolbar’s Ablebits data option, you will find the various options to be loaded up. Click on Trim Spaces.
- 4After clicking on trim spaces, a list of all the functions will open up. From that, you can select one of more than one function to be performed. You will have the option to Trim leading and trailing spaces, trim spaces between words to 1, and trim and remove non-breaking space. You can select all the options if you want or go with one of them. Once you have selected the options, click on trim. After you have clicked on the trim option, you will find that all your data has been arranged. You will not have to repeat the steps repeatedly, and at once, all the data will be arranged.
Conclusion
These were some of the methods to help you clear the spaces in the Excel sheet. All of these methods are meant for different purposes and removing different types of sheets. If you are using the add-on method to remove spaces, it will be the most effective method to help remove spaces.
