How to Remove All Spaces in Excel - Compute Expert

# How to Remove All Spaces in Excel

Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> How to Remove All Spaces in Excel

From this tutorial, you will learn how to remove all spaces in excel by using two methods.

When working in excel, we may sometimes need to remove all spaces in our data, leading, trailing, and in between, so we can clean them properly for further data processing. However, unfortunately, there are no special built-in excel features or functions that can do that for you.

There are, however, some methods to do it in excel if we look hard enough. Want to know what are these methods and understand the steps to implement them? Let’s read this tutorial further for that.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

## How to Remove All Spaces in Excel with a Formula: SUBSTITUTE

The first method to remove all spaces in excel is by writing a formula with the SUBSTITUTE function. Here is the step-by-step guide to writing the formula.

1. Type an equal sign ( = ) in the cell where you want to put your data without spaces in it

2. Type SUBSTITUTE (can be with small and large letters) followed by an open bracket sign

3. Input the data you want to remove all spaces from, followed by a comma sign ( , )

4. Type double quotes ( ““ ) with a space inside them followed by a comma sign

5. Type double quotes again, this time without a space inside them, followed by a close bracket sign

6. Press Enter
7. Done! SUBSTITUTE should immediately produce your data without all of the spaces in it

## How to Remove All Spaces in Excel with an Excel Feature: Find and Replace

We can also remove all spaces from our data by using the find and replace feature in excel. Here are the steps to do that.

1. Highlight the cell range that contains all the data you want to remove all spaces from

2. Go to the Home tab in your ribbon, click the Find & Select dropdown button, and choose Replace…. Alternatively, you can just press Ctrl + H on your keyboard

3. In the Find and Replace dialog box that shows up, type a space in the “Find what” text box

4. Leave the “Replace with” text box blank

5. Click Replace All

6. Done!

## Exercise

After you have learned how to remove all spaces in excel by using a formula and an excel feature, let’s do an exercise to deepen your understanding!

### Instructions

Do each instruction in the appropriate gray-colored cell according to the instruction number.
1. Remove all the spaces by using a formula!
2. Remove all the spaces by using an excel feature!
3. Remove all the spaces by using a formula and an excel feature!

To remove all excess spaces instead, use the TRIM function.

Related tutorials you should learn too:

Get updated excel info from Compute Expert by registering your email. It's free!