Remove spaces in Microsoft Excel
One of the advantage of Excel is it provide multiple ways to remove out spaces in the respective Microsoft Excel. The first method to remove extra unwanted spaces is none other than with the help of the "FIND" and with the "REPLACE option (Ctrl + H)" efficiently. And we need to put out the space and replace with a Blank (Nothing kept). And by doing this, it will remove out all the spaces which are present anywhere from the selected cells.
Moving ahead, we can easily remove the spaces with the help of the essential function present in Microsoft Excel, the "TRIM function". It is very much capable of removing out the spaces that are there at the starting of the cell as well as at the end of the selected cell content.
Besides these, we can also make use of the SUBSTITUTE function, where we can easily substitute the space with a blank in order to remove the spaces entirely from there.
Different Types of Spaces
The different types of spaces that are available in Microsoft Excel are as follows:
Moreover, we can quickly get rid of these above-mentioned types of spaces quickly and easily with the below-mentioned options effectively:
The respective "TRIM function" is considered the prebuilt integrated functions categorized under the Text functions efficiently. And it primarily removes all extra spaces from particular text except a single space between words in the Microsoft excel sheet.
Syntax or formula for the respective TRIM FUNCTION:
The respective "Trim function" in Microsoft Excel has only one compulsory argument or parameter, which is none other than the text.
The TRIM function in Microsoft Excel only removes the ASCII space character (32) from the given text.
How to remove out the Extra Spaces in the Microsoft Excel sheet?
The Removal of the extra spaces in Microsoft Excel is a straightforward task to perform in Microsoft Excel. Now let us understand how to remove the additional spaces in Microsoft Excel with the help of the various examples, which are efficiently discussed below.
# Example 1: Removal of the Excel Spaces by using Find and Replace options in the Microsoft Excel
From the below-mentioned example, we have a particular dataset in column D which will contain all three types of extra spaces, which are none other than the following ones:
Moreover, for the purpose reference, we have done a number of character counts for the column D datasets efficiently. And with the help of the LEN function in the particular column that is column E, we have just taken out this specific amount of the raw data to another new column that is column H, to apply the Find and Replace task effectively, as it was seen in the below-attached screenshot.
After that, we have to select the respective cell range where the Find and Replace parameter needs to be applied to remove the extra spaces in Microsoft Excel, as seen in the screenshot below.
Now, just after the previous steps, to activate the "find and replace option", we will efficiently press out the shortcut key, the Ctrl + H. After pressing the shortcut key, we will find and replace a window on our screen, as seen in the screenshot below.
Now, after that, in the Find what options: the input bar, we will insert out the blank space with the help of the space bar once, and in the Replace with there is no need to input anything, and it should remain blank. After that, we will be clicking on the Replace All button, as depicted in the below-attached figure.
Soon after performing the above steps, it will replace all the blank spaces in the cell, as seen in the screenshot below.
Now after that, we can find out the difference in character count between both the datasets. In which the respective "Find and Replace" basically remove out all the available blank spaces, and in the extra in-between spaces content also, it has removed all the particular empty spaces without retaining a single space between words, and this can be rectified with the help of the TRIM function. It can be seen in the below-attached screenshot.
Note: This method could only be used when we want to remove the leading and trailing type of extra spaces from the selected cells or to remove all the spaces in the data range from Microsoft Excel altogether
# Example: 2 Remove the Excel Spaces with the help of the TRIM Function
As in the below-mentioned example, we have a particular dataset in column that is Column D that contains all three types of extra spaces, which means out the leading spaces, trailing spaces, and the additional in-between spaces.
And for reference purposes, we have done a number of character counts for the column D datasets. And with the help of the LEN function in column E, we have taken out this raw data to another new column G column to apply the TRIM Function. And the Microsoft Excel TRIM function can remove all text spaces except a single space between words, as depicted in the screenshot below.
After that, in cell H4, let's apply the TRIM function, as seen in the screenshot below.
Now in the Formulas tab, let us click on the Text drop-down menu in the Functions section and select TRIM, as seen in the screenshot below.
Now, after that, the function argument dialogue box get appears on the screen, and then after that, we will click on the cell G4, which contains the text where we would like to remove all the spaces to complete the function, then click on the OK button, as it could be seen in the below-attached screenshot.
Now, we can observe that the particular cell is cell H4, where the trim function removes the leading space in that cell.
Similarly, the TRIM function is applied to the other range by selecting cell H4 and dragging it to cell H9. Then we will click on the" Ctrl+D" to apply to the rest of the cells, as seen in the screenshot below.
Now we can find out the difference in the respective character count in between both the datasets where the TRIM Function removes all the particular spaces before and just after the text in the specific cell, which means the cells G4, G5, G6 and G7 as well as the consecutive spaces in the middle of the string in the cell G8 and the cell G9, as seen in the below depicted screenshot.
# Example: 3 Remove out the Excel Spaces with the help of the SUBSTITUTE Function in the Microsoft Excel sheet
In the Microsoft Excel it will eliminate out all the extra Microsoft Excel spaces, including the single spaces between words or the numbers.
As from the below-mentioned example, we have taken out the dataset in column D, which basically contains all the three types of extra spaces:
And for reference, we have done several character counts for the particular column D datasets. And with the help of the function that is the LEN function in column that is E, we have taken this raw data to another new column G column to apply the SUBSTITUTE Function in it.
After that, we will apply the SUBSTITUTE Function, as seen in the screenshot below.
And now, in the Formulas tab, we will click on the Text drop-down menu in the Functions section and select the SUBSTITUTE FUNCTION.
Now the function argument dialogue box get appears on the screen:
As it can be seen in the below depicted screenshot:
Now, after that, it can be observe that in cell H4, in which the Substitute function removes the leading space in that cell, as seen in the below depicted screenshot.
And the "SUBSTITUTE Function" is applied to other ranges by selecting out the cell H4 and then dragging it till cell H9 and then after that, we will click on "Ctrl+D" so that it is applied to other cells also, as this could be seen in the below-attached screenshot.
After that, we can find out the difference in character count between both the datasets. And the Substitute Function primarily removes out all the blank spaces; in the extra in-between spaces content, and it has removed all the empty spaces without retaining of the single space in between of words, as depicted in the below attached figure.
Things to Remember about Excel Remove Spaces in the Microsoft Excel
The few important things which need to be remembered by any individual who are working with the remove spaces in Microsoft Excel are as follows: