MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace Spaces With Empty

May 18, 2018 - by Bill Jelen

Replace Spaces With Empty

Frank from New Jersey downloads data every day where the blank cells actually contain one or more spaces. This is frustrating because =ISBLANK() won't recognize these cells as empty.

You would think this would be easy to fix. But until Excel offers a proper =NULL() function, there is not a good way to get rid of the spaces. In Frank's case, other cells in the column contain spaces between words, so you can not simply use Find & Replace to remove all spaces.

Someone is mashing down the space bar and unknown number of seconds to clear out these entries.
Someone is mashing down the space bar and unknown number of seconds to clear out these entries.

I had hoped I could use Power Query to convert the spaces to Nulls. And I swear that it worked when I tried it in Frank's precence during a recent Power Excel Seminar. But it is not working today. So, there has to be a better way.

  • Select any heading cell and turn on the Filter with Data, Filter.
  • Open the Filter dropdown for the column with spaces.
  • Clear the checkbox next to (Select All) at the top of the list.
  • Scroll to the bottom of the list. This is encouraging… All of the entries with spaces are rolled in to one entry that says (blanks).

    The Filter drop-down sees any cell that contains only spaces as (blank)
    The Filter drop-down sees any cell that contains only spaces as (blank)
  • Select the (blank) entry and click OK.
  • Select all of the cells that are visible in the column (below the heading).

    Select the cells contains spaces
    Select the cells contains spaces
  • Press the Delete key to delete those entries

    Notice the LEN() function now shows 0
    Notice the LEN() function now shows 0
  • Clear the Filter by clicking the Filter icon in the Data tab.

If you have a faster way to solve this problem, leave a note in the YouTube comments.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2206: Replace Spaces with Empty.

Hey, today's question's from Frank in New Jersey. Frank is downloading the data over here in columns A through F, and see, Question 4 is one where they can type in an answer, but we're not getting empty cells. Here's the length of F2. Instead of getting empty cells, someone's mashing down the space bar. Like, here they typed Space+Space, here they typed Space+Space+Space, here just one Space, here's six Spaces. And, you know, the whole goal is here is we want to figure out how many of those have an answer. So, we're going to look down here in the status bar, where it says there's 564 answers to Question 3, and 564-- so, it's counting those spaces. I want to replace those spaces with a truly empty cell; and, you know with it being "Power Query Week," I thought we could finish off with a simple one-- and it's not behaving the way that I want it to behave.

So, you can try to write a formula out here, like the TRIM(F2)-- the TRIM(F2)-- and I can see that that's going to end up with a 0 length-- the TRIM(F2). But, if I try and Ctrl+C, and then Paste; Special; Values; I still end up with 563 answers, it's not truly getting rid of that cell.

Alright, so here's what I'm going to come up with. Frank says he's currently doing this with a whole bunch of Find and Replaces where you have to choose "Find entire cells," you must look for, you know, six spaces and replace with nothing. Instead, I'm going to turn on the filters, and I'm going to come here, and uncheck Select All, go all the way down to the bottom, and choose this one thing called "(Blanks)," which amazingly chooses things that have one space, two spaces, six spaces, the whole thing. Once I have that, I select those cells, simply press Delete-- see now, all those lengths changing-- clear the filters, go back, and now we have 547 answers, minus adding 546.  So, that's my way to replace those spaces with truly empty cells.

Alright, check out my new book, MrExcel LIVe: The 54 Greatest Tips of All Time. Click the "I" right in the corner for more information.

Wrap-up for today-- Frank from New Jersey downloads data where the empty cells contain some number of spaces, and wants those truly to be empty. I tried running a formula like TRIM(F2), but Paste Values does not give us those empty cells-- and you can tell they're not empty because you can select the column and look in the status bar to get a count of how many there are. It would be really nice if Excel would give us a NULL function that would truly return the empty cells. So my solution today-- Filter; choose Blanks; select the whole return range; and then press Delete-- delete being a fast way to delete the cell. If you want to try this yourself-- and boy if you have a faster way, please let me know in the YouTube comments-- to download the workbook from today's video, visit the URL down in the YouTube description. I want to thank Frank for that question, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: replace-spaces-with-empty.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Copy/paste in haste makes waste"

Title Photo: Annie Spratt on Unsplash

Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.