{"id":8879,"date":"2013-12-28T17:48:15","date_gmt":"2013-12-28T17:48:15","guid":{"rendered":"http:\/\/jurnsearch.wordpress.com\/?p=8879"},"modified":"2013-12-28T17:48:15","modified_gmt":"2013-12-28T17:48:15","slug":"excel-are-those-blank-cells-really-empty-how-to-scrub-them-quickly-then-fill-them-with-text","status":"publish","type":"post","link":"https:\/\/jurn.link\/jurnsearch\/index.php\/2013\/12\/28\/excel-are-those-blank-cells-really-empty-how-to-scrub-them-quickly-then-fill-them-with-text\/","title":{"rendered":"Excel: Are those blank cells really empty? How to scrub them quickly, then fill them with text"},"content":{"rendered":"

Another handy hard-won tip for MS Excel 2007, which may save time for someone new to the software. This shows how to blank cells in Excel that are not really<\/em> blank (because a formula left some hidden junk in them). <\/p>\n

Scenario: You tried copying the data and pasting it back in again, as “Values”. It had no effect. You removed trailing and stray spaces. With no effect. You still couldn’t get Excel to select all the blank cells. Here’s what worked for me, without running more formulas or macros:<\/p>\n

1. Download the free ASAP Utilities<\/a> add-on for Excel. Install, and locate its top button bar.<\/p>\n

2. Select all cells in your target column.<\/p>\n

3. In ASAP Utilities choose: Numbers & Dates | “Convert unrecognised numbers (text?) to numbers”. This has the very useful side-effect<\/a> of clearing all the hidden junk that may be clogging up your apparently blank cells.<\/p>\n

4. Now in Excel you can go: Home | Find & Select | Go To Special… | Blanks | OK. All your blank cells will be selected and highlighted, whereas before this operation would not find all the blank cells.<\/p>\n

To place a text marker into all your selected blank cells: press F2, then type a word or phrase into the first selected cell. Then, instead of pressing Enter as normal, hold down Crtl on the keyboard + press Enter. All your blank cells will then be filled with the typed text.<\/p>\n

ASAP Utilities also has a simpler “fill blank cells” option, but it seems to be unable to handle more than 400 rows at a time.<\/p>\n","protected":false},"excerpt":{"rendered":"

Another handy hard-won tip for MS Excel 2007, which may save time for someone new to the software. This shows …<\/p>\n

Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-8879","post","type-post","status-publish","format-standard","hentry","category-jurn-tips-and-tricks"],"_links":{"self":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts\/8879","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/comments?post=8879"}],"version-history":[{"count":0,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts\/8879\/revisions"}],"wp:attachment":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/media?parent=8879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/categories?post=8879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/tags?post=8879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}