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
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
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.-
Type an equal sign ( = ) in the cell where you want to put your data without spaces in it
-
Type SUBSTITUTE (can be with small and large letters) followed by an open bracket sign
-
Input the data you want to remove all spaces from, followed by a comma sign ( , )
-
Type double quotes ( ““ ) with a space inside them followed by a comma sign
-
Type double quotes again, this time without a space inside them, followed by a close bracket sign
- Press Enter
-
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.-
Highlight the cell range that contains all the data you want to remove all spaces from
-
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
-
In the Find and Replace dialog box that shows up, type a space in the “Find what” text box
-
Leave the “Replace with” text box blank
-
Click Replace All
-
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!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.- Remove all the spaces by using a formula!
- Remove all the spaces by using an excel feature!
- 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:
