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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using a Formula, Step 1

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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using a Formula, Step 2

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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using a Formula, Step 3

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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using a Formula, Step 4

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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using a Formula, Step 5

  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 - Screenshot of Removing All Spaces by Using a Formula, Step 7






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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 1

  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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 2

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

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 3

  4. Leave the “Replace with” text box blank

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 4

  5. Click Replace All

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 5

  6. Done!

    How to Remove All Spaces in Excel - Screenshot of Removing All Spaces by Using an Excel Feature, Step 6



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!

Download the exercise file and do all the instructions below. Download the answer key file if you have done the exercise and want to check your answers or when you are confused about how to do the exercise!

Link to the exercise file:
Download here

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!


Link to the answer key file:
Download here



Additional Note

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



Related tutorials you should learn too:



Want to Learn More About Excel?


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





Learn Excel Dashboard Course

Want to Learn More About Excel?


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




Follow our tutorial content also on


Excel Calculation

Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division in Excel

Average in Excel



Excel Formula

VLOOKUP Function in Excel

IF Function in Excel

SUM Function in Excel

COUNTIFS Function in Excel

SUMIFS Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text in Excel

Excel Worksheet Definition

Excel Range Definition

Excel Shortcuts



Excel Products & Services Recommendation

Best Laptops for Excel

Best Tablets for Excel

Best Keyboards for Excel

Best Mouse for Excel

Best Monitors for Excel



Excel Consultation

Recommended Work Tools

About Us

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2022 Compute Expert