Replace Spaces With Empty
May 18, 2018 - by Bill Jelen
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.
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).
- Select the (blank) entry and click OK.
Select all of the cells that are visible in the column (below the heading).
Press the Delete key to delete those entries
- 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.
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