Excel Space Challenge: Replace Spaces with Empty Cells - Episode 2206
3,650 views · Published 21 May 2018 · 2:57 · Indexed 4 November 2026
Channel: MrExcel.com · 2018 · Education
Microsoft Excel Tutorial: Replaces Spaces with Empty Cells in Excel Welcome to another episode of the MrExcel Podcast, where we tackle your Excel questions and provide helpful tips and tricks. In today's episode, we have a question from Frank in New Jersey about replacing spaces with empty cells in his downloaded data. This is a common issue that many Excel users face, and I'm excited to share a simple solution with you. Frank's data has a column where users can type in their answers, but instead of getting empty cells, there are spaces. And not just one or two spaces, but multiple spaces that are making it difficult to accurately count the number of answers. We want to find a way to replace these spaces with truly empty cells, and I have just the solution for you. As we continue with "Power Query Week," I thought it would be fitting to use a simple Power Query technique to solve this problem. You could try using a formula like TRIM(F2), but even after pasting values, the cells still show a length. So, here's what I came up with: turn on the filters, uncheck "Select All," and choose "(Blanks)." This will select all cells that appear empty, even if they have spaces. Then, simply press delete and clear the filters. Voila! The spaces are now replaced with truly empty cells. I know some of you may be thinking, "Why not just use Find and Replace?" Well, that's what Frank was doing, but it can be a tedious process, especially if there are a lot of spaces to replace. My solution using Power Query is much faster and more efficient. If you have a different method that works for you, please share it in the comments below. Before we wrap up, I want to mention my new book, MrExcel LIVe: The 54 Greatest Tips of All Time. It's packed with valuable Excel tips and tricks that will save you time and make you more efficient. Click the "I" in the corner for more information. In conclusion, Frank from New Jersey had a common issue with his downloaded data, where empty cells contained spaces instead of being truly empty. We solved this problem by using a simple Power Query technique that quickly replaced the spaces with empty cells. If you want to try this yourself, you can download the workbook from the link in the YouTube description. Thank you, Frank, for your question, and thank you for tuning in to another episode of the MrExcel Podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest videos. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoftexcel #exceltricks #excelhacks #excelbug #evergreen This video answers these common search terms: how to trim blank spaces in excel remove spaces in excel how to remove spaces in column in excel how to remove spaces in an excel column how to remove trailing spaces in excel how to remove all spaces in excel cell how to remove hidden spaces in excel how to remove spaces from cells in excel how to remove spaces in excel can you remove all spaces in excel how do you remove spaces in excel cell Table of Contents: (00:00) Replace Spaces with Empty Cells IN Excel (00:23) Solution using filters and delete function (01:00) TRIM does not solve it (01:30) Using Filters to solve (02:06) Recap of problem and solution (02:30) Clicking Like really helps the algorithm Frank from NJ downloads data where the "empty" cells contain some number of spaces Frank wants to replace the spaces with truly empty cells. Writing a formula and then Paste Values does not give us empty cells (Look in the status bar to get a Count of how many non-blank cells there are) Excel really needs to offer a =NULL() function to return empty cells My solution: Filter. Choose Blanks. Select. Press Delete To download this workbook: https://www.mrexcel.com/download-center/2018/05/replace-spaces-with-empty.xlsx Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154035/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920