While typing it’s possible that by mistake you add few extra spaces in excel, these could be leading spaces, trailing spaces or spaces in between text and numbers.

This results in the data looking visually distorted and less organized. It also leads to issues when you’re trying to filter for certain values or use formulas on data.

To solve this let’s see how to remove spaces in excel be it to remove leading spaces in excel or to remove space in excel before text.

Trim Function in Excel | Remove Leading & Trailing Spaces

Let’s first learn what are leading and trailing spaces:

Leading spaces: are spaces in the front of the first character of the value.

Trailing spaces: are spaces at the end of the last character of the value.

We have the following data:

Some cells have leading spaces some have trailing spaces, and some have both. Now we’ll use the trim function in excel which will removes leading and trailing spaces.

1. Go to cell C2 and type =TRIM(A2).
   
   
   
2. Hit enter and you’ll see that the leading space is removed.
   
   
   
3. Now drag the formula in C2 down to rest of the cells.
   

Now you know how to remove space in excel before text and how to remove spaces in excel after text.

How to delete space in excel using find and replace option

Now apart from using the trim function in excel there is one more way to delete space in excel by using find and replace option.
This is mostly used to remove space in excel between numbers or text.

First, we’ll find space in excel and then we’ll replace the space in excel with nothing or no character.

1. Select the cells from which you want to delete space in excel.
   
   
   
2. Use shortcut CRTL+H to open the Find and Replace dialog box.
   
   
   
3. Under Find What field enter a space that is “ “ and keep Replace With field blank. The select Replace All button.
   
   
   
4. You can see that the extra space between the numbers is removed.
   

This also removes leading and trailing spaces from the data if any.

Remove Spaces in Excel using SUBSTITUE function

Another way to remove all spaces in excel is by using the SUBSTITUTE function.

The function has 3 arguments:

1. Text: this is the value on which you want to use the SUBSTITUTE function
2. Old value: this is the value you’re looking to replace.
   In this case it’ll be space that is “ “
3. New value: this the new value you want.

Let’s see it in action:

1. In cell B2, type =SUBSTITUTE(A2,” “,””) and hit enter.
   
   
   
2. You can see that the function replaced the space with nothing.
   

You can drag the formula to rest of the cells to remove all spaces.

Remove Extra Space Between Text

In the below data you can see that there are extra spaces in between the text.

We want to only have 1 space between words and want to remove the extra spaces.

So let’s see how to remove extra space in excel between text by using the find and replace option.

1. Select the cells.
   
   
   
2. Use shortcut CRTL+H to open the Find and Replace dialog box.
   
   
   
3. Under Find What field enter 2 spaces that is “  “ and under Replace With field enter one space that is “ “. The select Replace All button.
   
   
   
4. Now depending on the number of extra spaces, you might have to repeat the above steps until you see the error message “Microsoft Excel cannot find matching data to replace.”
   

This is how Excel remove spaces works.

Other Important Formulas

Here’s are some other important tutorials which might help you:

1. 3 Quick ways to remove blank rows in excel
2. Counting characters in excel using LEN function
3. 3 Quick ways to remove duplicates in excel
   
   

Conclusion

That’s all about excel remove spaces.

We covered how to remove space in excel before text, how to remove trailing spaces in excel and much more.

Hope now you know how to remove spaces in excel using the trim function as well as the find & replace option.

In case you have any questions, you can write them down in the comment section and our team will try to answer as many as we can.