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:

1. Leading Space: Leading spaces in Microsoft Excel contain the extra amount of spaces just before the test strings in any particular cell and are only termed the Leading spaces. As it can be easily depicted in the below-attached screenshot.
   
2. Trailing spaces: The space in Microsoft Excel that primarily contains the extra amount of the spaces just after the text string in any of the particular cells of the excel sheet is only termed as the Trailing space. As it can be easily depicted in the below-attached screenshot.
   
3. Various Datasets may contain extra in-between spaces, meaning the additional spaces between text, as the below-attached figure could easily understand.
   
4. In Microsoft Excel, various Datasets may also contain Line breaks with extra space, as depicted in the screenshot below.
   

Moreover, we can quickly get rid of these above-mentioned types of spaces quickly and easily with the below-mentioned options effectively:

1. FIND AND REPLACE in the Microsoft Excel.
2. TRIM Function available in Microsoft Excel.
3. SUBSTITUTE Function, which is available in Microsoft Excel.
4. The CLEAN as well as the TRIM functions basically remove the Extra Space of Line Breaks in Microsoft Excel.

TRIM Function

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.

• Text: It is the cell content from where we need to remove the extra spaces in Microsoft Excel.

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:

1. Leading spaces.
2. Trailing spaces and the extra in-between spaces.

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:

• The leading spaces.
• The trailing spaces.
• The Extra in-between 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:

1. Text Click on the first cell in the respective column, which we need to be changed out effectively.
2. And in the Old text, we need to enter out the character that's needs to be replaced; and it should be entered in between of the double quotes, which can be for the Removal of spaces. It will be" ".
3. And now, in the new text, we did not want to replace anything; and therefore, it can be typed as ""respectively.

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:

1. Since it was known that the extra space is also considered to the character, and therefore in the respective ASCII (American Standard Code for Information Interchange) encoding, it has code number 32 efficiently.
2. Sometimes, some of the data might appear as line breaks that may primarily contain the extra spaces between words. And to remove all those additional spaces, we can easily combine them with the Excel TRIM and then use CLEAN function to remove out the Extra Space of Line Break, respectively.