How to Remove Spaces in Excel

When managing extensive datasets in Excel, ensuring data consistency is crucial. An all too common inconsistency is unwanted spaces — whether they creep in at the beginning or end of a text string, appear as unwarranted gaps between words, or are unintentional space characters throughout your content. These unnecessary spaces can disrupt data analysis, make lookups fail, or simply make the data appear untidy.

Text data can often be messy and inconsistent, especially when it's sourced from different places or input manually. Extra spaces may seem harmless, but they can cause issues when you're trying to sort, filter, or perform other operations on your data.

Fortunately, Excel offers a rich array of tools and functions tailored to help you manage and clean your data. This tutorial will explore various techniques to help you seamlessly eliminate unwanted spaces from your data cells.

How to Remove Leading Spaces, Trailing Spaces, and Extra Whitespaces in Excel With the TRIM Function

The TRIM function in Excel is specifically designed to tackle the common issue of unwanted spaces that often creep into our data. Whether it's a space at the beginning of a text (leading), at the end (trailing), or those unnecessary extra spaces between words, TRIM is your go-to function. For instance, if you have the characters ” Olive   Baker “ in a cell, applying the TRIM function will transform it to “Olive Baker”, removing the leading, trailing, and extra spaces between the words.

1. Select the neighboring cell where you want to apply the TRIM function
    
   
   
2. Write the function =TRIM(B3), replacing B3 with the reference cell
    
   
   
3. Drag down the + symbol to apply the TRIM function to other cells in the same column
    
   
   
4. This will copy the text from the reference cells, removing all leading spaces, trailing spaces, and extra whitespace
    
   Optional: After doing this you can use “Copy” and “Paste as Text” to replace the original cells and clear the cells where the TRIM function was applied.
    
   
   

How to Remove spaces in Excel with the SUBSTITUTE function

While the TRIM function is excellent for general space removal, the SUBSTITUTE function offers a more targeted approach. With SUBSTITUTE, you can replace specific characters or spaces with another character or even remove them entirely. For example, if you want to remove all spaces in ”  Olive Baker  “, SUBSTITUTE can help you get “OliveBaker”. Moreover, if you wish to remove specific spaces or replace them with other characters, this function provides the flexibility you need. 

The SUBSTITUTE function in Excel offers targeted control, allowing users to replace specific spaces or characters with others, making it ideal for nuanced data adjustments. On the other hand, the Replace feature (see next method) is a broader tool that swiftly removes all single spaces in the selected data, making it suitable for quick and comprehensive space elimination. While SUBSTITUTE provides precision, Replace offers simplicity and efficiency.

1. Select the neighboring cell where you want to apply the SUBSTITUTE function
    
   
   
2. Write the function =SUBSTITUTE(B3,” “,””) , replacing B3 with the reference cell in your table
    
   
   
3. Drag down the + symbol to apply the SUBSTITUTE function to other cells in the same column
    
   
   
4. This will copy the text from the reference cells, removing all spaces, wherever they are located
    
   Optional: After doing this you can use “Copy” and “Paste as Text” to replace the original cells and clear the cells where the SUBSTITUTE function was applied.
    
   

How to Remove All Spaces in Excel With the Replace Feature

There might be instances where you want to remove all spaces from your data, including those between words. In such cases, Excel's built-in Replace feature comes to the rescue. By using this method, you can quickly search for all single spaces in your data and replace them with another character or nothing, which means effectively removing them. For instance, ”  Olive Baker  “ becomes “OliveBaker” after applying this method with no replacement character. It's a straightforward and efficient way to ensure that no spaces remain in your dataset.

The Replace feature in Excel is designed for straightforward and comprehensive removal of all single spaces in selected data, offering users a quick solution for tidying up datasets. In contrast, the SUBSTITUTE function (see previous method) provides a more nuanced approach, enabling targeted replacement of specific spaces or characters with others. While Replace emphasizes efficiency in wholesale space removal, SUBSTITUTE ensures precision for specific character substitutions.

1. Select all cells where you want to remove or replace spaces in
    
   
   
2. Click “Home” in the Ribbon, then “Editing” and select “Replace”
    
   Alternatively, you can use the hotkey “CTRL + F” and switch to the “Replace” tab.
    
   
   
3. Use “Replace All” to remove all space characters
    
   Enter a single space character in “Find what”, nothing in “Replace with” and click “Replace All”.
    
   
   
4. Excel will let you know how many spaces were found and removed.
    
   

Extra: How to Add Shading to Alternating Rows in Excel

It's an old trick at this point, but applying shading (zebra stripes) to alternative rows in Excel makes your sheet easier to read. The effect, also known as banded row, allows your eyes to keep their place more easily when you're scanning a spreadsheet. The difficulty, then comes in knowing where to look and how to format cells as a table in the first place. In our other guide, we show you how to apply and customize table formatting to form alternating rows in Excel.
 

Extra: How to Divide in Excel

In Excel, you can divide using cell references, handle errors like dividing by zero, and even combine division with other operations for more complex calculations. In our other guide, you will learn basic to advanced functions for division, handle errors, and optimize calculations for maximum efficiency.
 

Extra: How to Remove Table Formatting in Excel

In Excel, you can apply predefined table styles, making your data presentation-ready. However, there are instances where you might want to strip away the table formatting without losing the underlying data. In our other guide we show you various techniques to remove table formatting in Excel.
 

Extra: How to Structure Collected Data in Excel

To make the most of Excel's features, you need to structure your data properly. Data structure refers to how you organize your data in a spreadsheet. A good data structure makes it easy to perform calculations, filter, and sort data, create charts and pivot tables, and apply formulas and functions. In our other guide, we show you how to structure collected data in Excel using some best practices and tips.