How to remove spaces in Microsoft Excel

It is well known that when we copy some of the data from external sources and then paste it into the Microsoft Excel sheet, we will get some of the extra space associated with it, which could be leading, trailing, or in-between spaces. "Extra space" is a common problem that makes the data unreadable and meaningless.

And in Microsoft Excel, there are primarily three different techniques that can be used to remove the "extra spaces" from the respective Excel data in an effective manner. Microsoft Excel also offers a TRIM () function to remove a specific number of spaces from the Excel data. In this tutorial, we will describe these methods to remove extra spaces from the given data.

Microsoft Excel data with spaces

Now we will see examples of the Excel data (for a given string Good Morning!) with extra and unnecessary spaces.

Change below image:

1. The above figure shows that, in the second row, there is no extra space contained by the data that is none other than Good Morning!).
2. And the next row looks OK as compared to the second row. But, four spaces are added at the end of the string (Good Morning!), and these spaces are known to be the "trailing spaces," which are not easy to spot.
3. Leading spaces are easy to spot, as we can also see there are few spaces in third-row data.
4. In the end, see the space between the words or characters. This is an example of in-between space.

What are the three basic techniques to remove extra spaces in Microsoft Excel?

The three basic techniques which can be used for the purpose of removing the extra spaces in Microsoft Excel are as follows:

1. Remove spaces by making use of the TRIM () function.
2. Remove spaces with the use of the Find and Replace method.
3. Remove spaces by just making use of the Power Query.

How to remove spaces by making use of the TRIM () function?

Extra space in data is a very common problem. Sometimes, they make the data unreadable and pointless if used in the wrong place. Thus, we have a solution for it.

Moreover, the respective TRIM () function is a predefined function in Microsoft Excel that can remove the extra spaces from the given data. This function is easy to use in Excel. It also removes all the extra spaces from the word. These extra spaces can be leading, trailing, or in-between spaces; it removes all. It finds the extra spaces in words and removes them.

Syntax

=TRIM(cell_number)

Look for an example below:

• Example

We have an Excel dataset that usually contains some extra spaces.

Change below image:

Now, after that, we will use the TRIM() function on each cell of this dataset and then remove the extra spaces from them.

Trim B2 Cell

We can see that the B2 cell string does not contain any extra space, as we already mentioned in the information.

Now we will be executing the following formula for B2 cell to see how it also works with the normal string.

=TRIM (B2)

Change the below image:

After that, we will press the "Enter key" from our keyboard to get the trimmed result respectively.

Change the below image:

Trim B3 Cell

Now, we need to change the cell number as a parameter value inside the TRIM () function and will see the result.

=TRIM(B3)

Change the below image:

After that, we must press the "Enter key" from our keyboard and get the new string trimmed.

Change the below image:

Similarly, when we are performing this respective TRIM () operation with the other cells which are containing out the extra spaces, all of the extra will be get removed, and we will get the same result as depicted in the below-mentioned image:

Change the below image:

And one of the biggest advantages of using the "TRIM () function" in Microsoft Excel is that it is used to remove only the extra spaces from the data instead of all the necessary spaces from them.

How to remove spaces by making use of the Find and Replace method?

There is one more simple way that can be efficiently used to remove the spaces from the given Excel data: The Find and Replace method. And by using the Find and Replace feature of Microsoft Excel, we can easily discover all the spaces and replace them with nothing to remove the spaces from it respectively.

We can also replace multiple values at once by selecting a cell range. The Find and Replace feature contains two fields: Find what and Replace with.

• Find what - In this field, the users need to provide the value for which they are looking to replace.
• Replace with - In this field, and the users need to enter the value with which they want to replace.

Let us now see, with the help of an example, how we can easily use this method to remove the space from our data respectively.

Example:

We have the following dataset in Microsoft Excel containing some of the extra spaces.

Step 1: First, we must select a single cell or range of cells from which we want to remove spaces.

Change the below image

Step 2: We must press the Ctrl+H shortcut key from our keyboard to open the Find & Replace dialogue box directly.

Change the below image:

Step 3: Here, in this step, we need to provide values in "Find what and Replace with field," respectively.

Remove two spaces with one -

1. Please navigate to the Find What field and press the Spacebar two times on our keyboard to add two space characters.
   
2. Similarly, we need to go to the Replace with the field, but this time, press the Spacebar only once to replace specified two spaces with one and then press Replace All
   

Step 4: When the replacement has been done, a popup dialogue box with a message will show us. Look at the below screenshot; 13 replacements are made.

And then, we are required to press the "OK" button and Close the Find & Replace panel window.

Change the below image:

"Repeat this replacement step until all extra spaces do not get replaced by one."

Step 5: After the successful replacement, we need to see that we get the following Excel dataset after removing space, as the spaces have been removed from selected cells.

Change the below image:

However, we will notice that some "extra spaces" have also remained. This is because only two spaces are replaced with one. This only works well if a string contains up to two spaces.

Remove all spaces -

Please navigate to the Find What field and simultaneously enter one space by pressing the Spacebar on our keyboard.

Nextly in Replace What field, we need to provide nothing, and then we are required to press Replace for one-by-one replacement while pressing Replace All button for all replacements at once.

See the below outcome that a total of 44 replacements have been made.

We can also see that all the spaces (leading, trailing, or in-between) have been removed. Data become a single word string with no space.

Change the below image:

Problem with Find & Replace method

1. This method fails when we only remove the extra spaces, not all.
2. We will remove two spaces with one to resolve this problem, but it also does not work when a string contains more than two spaces.

Solution

In that scenario, we will suggest using the TRIM() function that removes all the extra spaces from the given string respectively.

How can one easily remove extra spaces by using the Power Query?

In Microsoft Excel, the other most important method can be efficiently used to remove the extra spaces from the Excel data, and that is none other than the "Power Query Method." And this would be the last method we describe in this tutorial to remove spaces from the data held by the Excel cells.

Moreover, the Power Query method differs from the above two methods.

Power Query is basically all about data transformation, which can handle this task. So, let us now see how power query helps particular users effectively remove extra spaces from data.

Step 1: First, we must need to select a range of the data and then go to the Data tab in our Excel menu bar. We have chosen the other data for this example to accomplished it rather than using the previous data.

Change the below image:

Step 2: Now, in this step, inside the Data ribbon tab, we need to click on the From Table command respectively.

Change the below image:

Step 3: After performing the above steps, a dialogue box will appear on our screen with the selected range, and then we are required to click on the OK button.

Step 4: A power query editor will open on our screen, and our Excel data will be automatically turned into an Excel table.

Change the below image:

Step 5: Now, in this step, we are required to right-click on the table heading and will choose Transform, and then trim. Wait, it will wait to remove the extra spaces. We have to follow a few more steps to achieve this efficiently.

Step 6: We can see that the leading and trailing spaces have been removed here, but not in-between spaces.

Now, we need to close the power query editor panel by just clicking on the Cross button available at the top right corner of the Excel sheet.

Change the below image:

In the right panel, we can also see that the Trimmed text step is showing in the APPLIED STEPS section.

Step 7: A popup will show before closing this window, where we need to click on the Keep button to remain the changes.

Step 8: See that 7 rows have been loaded, and a table with no leading and trailing space will be created and displayed as below:

Change the below image:

And all the above discussed three methods are equally useful according to the user's need. But we will suggest an individual use Excel's TRIM() function to remove the extra spaces.