Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

4 Quick Methods for Deleting Spaces in Excel

    how to delete extra spaces in excel

    A clean data is our birthright. But sometimes, we are not able to access it.

    Sometimes, we are not able to use our data as the way we want to use it.

    And, the one biggest reason which makes our data unable to use is unwanted spaces in a cell. So, if you want to use it you have to delete these extra spaces.

    When you think about this, most of the time when you get data from ERP systems or you have some formulas which not written properly.

    Even, every time you work on your date you have to verify if there are any extra spaces. These extra spaces can give you pain in future so you need to remove them in the very beginning of your work.

    Today in this post, you will learn 4 Different Methods for Deleting Spaces in Excel.

    And, you will also learn that why all these methods are unique and usable in different situations.

    Table of Content

    1. Using Trim Function
    2. Combine Trim With Clean Function
    3. Add Substitute Function with Trim and Clean
    4. Use Find and Replace Option

    So let’s get started to explore these methods. And, please download this sample file to follow along.

    1. Using TRIM Function

    It happens sometimes that you get trailing and leading spaces in your data and it looks very nasty. But we have a very simple & easy method to pull out this garbage from our data.

    And, we need only one function to deal with it. TRIM FUNCTION.

    TRIM can remove all unwanted spaces from a text string except the spaces between two words.

    using trim function for deleting spaces in excel

    In above example, we have used TRIM Function to remove all the unwanted trailing and leading spaces from the cell.

    We have total 70 characters in the cell in which we have unwanted spaces & when I have used TRIM characters count is decreased to 41. That means it has deleted the 29 spaces from the cell.

    2. Combine Clean and Trim

    Now, there is also an another possibility that you’ll get a line break in your text string.

    In this situation, you need to use CLEAN function with TRIM. The clean function can remove all non-printing character which includes a line break as well.

    deleting spaces in excel with clean function

    In above example, we have used a formula by combining trim and clean to remove line break.

    How does it work?

    First of all, the clean function will remove all non-printing characters from a text string and then trim function will remove all unwanted spaces from text string other than space between two words.

    3. Using Clean, Trim, and Substitute

    Here we have an another type of issue with these white spaces.

    It happens sometimes that we want to remove all type of spaces from a cell. But clean and trim both are not able to remove all type of spaces.

    For example, if you have a non-breaking space in a cell, you will not able to deal with it using both of these functions. And for this problem, you need to combine CLEAN and TRIM with SUBSTITUTE function.

    Let me show you an example.

    In below example, we have a list of mobile numbers in a worksheet. And, these numbers are surrounded by some unwanted white spaces. we only need 10 digit mobile number from it.

    use substitute function for deleting spaces in excel

    But, by using both trim and clean we are not able to do that. That’s why we have to use substitute function.

    How this combination of Clean, Trim and Substitute Works Together?

    Substitute function can help you to find a specific character or a set of characters from a text string & replace with some other characters or a single character.

    4. Using Find and Replace Option

    Wait a minute I have another option to solve the above-mentioned problem. And, you don’t have to use a formula for that. You can use find & replace option to replace white spaces from a cell.

    Here are the steps.

    use find and replace for deleting spaces in excel

    1. Select the cell or range of cells from which you want to remove white spaces.
    2. Use the shortcut key, Control + H to activate find and replace option.
    3. In “find what” input bar insert a blank space using the space bar.
    4. And, “Replace With” should remain blank.
    5. Click on the replace all button.
    6. You’ll receive a pop-up message about how many blank spaces are replaced.

    Important Note: Use this method only in that situation when you are confirmed to remove all type of spaces from selected cells.

    Sample File

    download sample file to learn more about this tips

    Conclusion

    Extra spaces are always irritating. But now, you have 4 different methods to remove them. You can choose any of these methods which you think is perfect for you.

    I hope this will help you to play well with your data.

    Now tell me one thing. Do you know any other problem related to unwanted spaces in excel which I am now covering here? Or, you have any other way to deal with these problems.

    Please share with me in the comment box. I would love to hear from you.

    Related Function

    Related Tips



    • Ogundepo Ezekiel Adebayo

      You did a good job by teaching us excel. Thanks Punnet.

      • Puneet Gogia

        You’re welcome.