Replace Spaces With Empty

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.