5 Ways to Find and Remove Blank Spaces in Excel

Bottom Line: Learn 5 different techniques to find and remove spaces in Excel.  Spaces can cause formula errors and frustration with data preparation and analysis.

Skill Level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can download the workbook that I use in the video, including the macro I wrote to remove blank spaces.

Excel File Icon 2021 xlsx Remove Blank Spaces BEFORE.xlsm

Excel File Icon 2021 xlsx Remove Blank Spaces AFTER.xlsm

Space Hunting

Blank spaces can be a headache for data analysts and formula writers. That's because they are not easy to spot, yet can cause frustrating calculation errors.

Space characters can cause formula errors.

After many years of working with Excel, I've become a Space Hunter. That sounds like a sci-fi movie title, but it's just someone who can quickly locate and delete superfluous spaces in cell values.

Based on my space hunting experience, here are five techniques I use frequently to clean up data.

1. Find & Replace

The first method for space hunting is to use the Find & Replace feature.

After highlighting the cells that you want to search through, go to the Home tab. Then open the Find & Select dropdown menu. Select the Replace option. The keyboard shortcut for this is Ctrl + H.

Find and replace

That will bring up the Find and Replace window. On the Replace tab, place one blank space in the Find what field. Make sure there is nothing in the Replace with field. Hitting Replace All (keyboard shortcut: Alt+A) will remove any instances of a space in the data set that you selected.

Find and Replace all spaces

Although this method is really quick and easy, it's only useful for data where you want ALL spaces removed. However, there are times when you want to keep spaces between words. For example, if your cells contain both first and last names, you will probably want to keep the space in between those names.

That scenario leads us to our second method for space hunting.

2. The TRIM Function

The TRIM function removes all spaces in a text string, except for single spaces between words.

Below is an example of a report where some of the cells look like they are indented but the indentation is actually just extra spaces. Let's say we want to remove those spaces for uniformity's sake. We can use the TRIM function to do that.

Remove extra spaces using the TRIM function

In a blank column, start by typing the equals sign (=) and the word TRIM, then tab into the TRIM function.

The only thing you need to identify for the TRIM function to work is the text that needs trimming. So either type the name of the cell or click on the cell that you want to trim and hit Enter. This will return the result you're looking for: text with no spaces before or after it.

Trim function argument Text

When you copy the formula down for the whole column, the result looks like this.

TRIM function new column of data

As you can see, the spacing between the words remains in place, but any spacing before or after the text is removed.

With this method, we had to create a whole new column, so you would have to add the additional step of replacing the original column with the new one using Copy and Paste Values. You can then delete the column with the formulas.

3. Power Query

Like functions and formulas, queries can also be tripped up by the inclusion of a stray blank space.

Null values in Power Query caused by spaces

If you are unsure if blank spaces are what's causing the problem, you can click into the Editor's preview to see if there is a space or not.

Space after name shown in preview of power query editor

To remove the spaces, Power Query has a Trim feature found in the right-click menu. With the column that you want to fix selected, just right-click and choose Transform, and Trim.

Right click transform trim in power query

This trims all the blank space before/after the text string so that the query will return the correct values.

Tim data in order to return good values

If you are relatively new to Power Query, or could use a refresher, I recommend checking out my free webinar: The Modern Excel Blueprint.

Modern Excel Blueprint Training Webinar Excel Campus Jon Acampora

4. Macros and VBA

Let's look at the same example we used for the TRIM function, but this time we will use a macro to trim extra spaces from text.

Start by opening the VB Editor. Go to the Developer tab and select the Visual Basic Button. The keyboard shortcut is Alt + F11.

Open the VB Editor with Alt+F11

I've written a simple macro that loops through each cell and replaces the value of the cell with the trimmed value. If you'd like to copy this macro, you can find it in the download file at the top of this post.

Macro to trim text

One of the benefits of using a macro is that you can assign it to a button. Here I've assigned it to a button on the actual worksheet.

Trim Macro Button on Worksheet

And here, I've added it to the ribbon by including this macro in my Personal Macro Workbook and assigning a button to it.

Custom Excel Ribbon Button for Trim Macro VBA

That way, I can use this macro on any workbook.

One downside to using the macro method for removing blank spaces is that you cannot undo the action once the macro has been run. In that case, it might be a good idea to save the file before running the macro, if you think you might need to un-trim the data for some reason.

It's also important to note that the VBA Trim function does NOT trim extra blank spaces between words. Excel's TRIM function will remove additional spaces between words. So if there are two spaces between the first and last name, TRIM will remove the additional space. VBA's Trim will not do this. It only removes spaces from the beginning or end of the string. Thanks to Marvin (an Excel Campus Community Member) for bringing this to our attention!

Here are tutorials for how to create a Personal Macro Workbook as well as how to make Macro buttons, whether on the worksheet or in the ribbon.

How to Create a Personal Macro Workbook (Video Series)

How to Create Macro Buttons in Excel Worksheets

5. Trimming Other Characters

Sometimes after you have trimmed the spaces from your text, you might still get calculation errors. One reason for this could be that another hard-to-detect character might be in play. One such character is the non-breaking space. This is often used in HTML coding ( ) and is similar to a regular space, but would not be deleted by the TRIM function or our macro.

To determine which character might be gumming up the works, you can use the CODE function. By copying and pasting the invisible character into a blank cell, and then identifying that cell's text in the CODE function (“text” is the only argument that needs to be identified for the CODE function), you will return a code number for that character. A quick Google search of that code number will reveal what type of character you are dealing with.

Code Function

In this case, the non-breaking space character is code 160.

One way to remove that character from your text would be to use Find & Replace. You can simply copy the character and paste it into the Find what field.

Find and replace non-breaking space

You could also use the REPLACE function to do the same thing.

One other option, since we know we want to remove the last character in the text, would be to use the LEFT and LEN functions to essentially create a customized trim feature.

LEFT returns the leftmost characters in a cell, up to a designated amount. LEN (short for Length) returns the number of characters in a cell.

So combining those two functions and subtracting 1 from the LEN to remove that unwanted character would leave just the text without the additional character.

LEFT and LENGTH functions to remove unwanted end character

Conclusion

I hope this has been helpful for you. I also explain how spaces can cause errors in formulas in my video on an Introduction to VLOOKUP.

There are actually more than just five ways to go about this task, and if you'd like to add to the list, please leave a comment below. As always, you can ask questions there as well.

Thanks for following along. Until next time!

  • Jon: I am a retired civil engineer from the US Army Corps of Engineers. Although I am no longer actively analyzing data, I enjoy your excellent tutorials and often pass them along to family and friends. Keep up your great work! I wonder if you could show the tools you use to prepare the tutorials (short videos and screen shots). Usually my contacts need to prepare tutorials for their specific needs.

    • Hi Pedro,
      Thank you for your service and congratulations on your retirement. I appreciate your continued support.

      In regards to making the videos, we use Camtasia to record and edit the videos. We use Snagit for the screenshots. Both of these applications are developed by a company named TechSmith.

      There are a lot of solutions on the market for screen capture videos. Camtasia is great if you want to edit the videos and add layers for intros/outros, annotations, zoom & pan, etc.

      I hope that helps. Thanks again and have a nice day! 🙂

  • Hi!
    Everything quite clear, nice post.
    About character 160, I simply use a short VBA routine that trims first and checks for character 160:

    test = Trim(Worksheets(“C”).Cells(i3, 24).Value)
    ctc = Asc(Right(test, 1))
    If ctc = 160 Then test = Left(test, Len(test) – 1

    Best regards

  • Hi Jon,
    nice post as ever! I like the macro solution and brought it to my PMW. Thanks!
    I noticed that Trim just trims at the end of the strings. With the worksheet function it trims also multiple spaces in the middle of the strings:
    Application.WorksheetFunction.Trim(c.Value)

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >