We should all be using Go To Special to quickly remove rows containing blanks, but if those blanks are returned by a formula which uses the double quotes to denote a blank then you’ve got a problem because Go To Special thinks a blank returned by "" isn’t blank!
For example; a formula like this returns a blank if A1 is empty:
=IF(A1="","",A1)
In English the formula above reads:
IF cell A1 is empty, then return a blank, otherwise return the contents of A1.
One solution is instead of returning a blank with "", return an error with the NA function like this:
=IF(A1="",NA(),A1)
In English the formula above reads:
IF cell A1 is empty, then return the #N/A error, otherwise return the contents of A1.
With the NA() formula in column C every other row contains a blank based on the values in column A - see image below:
You might be thinking that instead of an invisible stain you now have an ugly #N/A, but at least #N /A is easily removed with very little elbow grease. I'll show you how.
Note: if you just wanted to get rid of the blanks in column A you'd simply select cells A1:A11 > CTRL+G to open the Go To dialog box > click 'Special' > choose 'Blanks' > click OK > delete selected cells. However I'm addressing the blanks returned by formulas in this post so we'll pretend column A isn't there 😉
Now, we'll use Go To Special to delete the rows containing #N/A:
- Select the cells C1:C11
- Press CTRL+G to open the Go To dialog box
- Click the ‘Special’ button, Note: it’s both special and called ‘Special’ 🙂
- Select ‘Formulas’ and ‘Errors’ as shown below then click ok.
Now all the cells containing errors are selected:
And you can right-click > Delete > Delete Entire Row:
So now you’re left with the clean data:
If you haven’t seen the Go To Special dialog box before you might want to take a few minutes to see what other gems you can ‘spy with your little eye’. Here it is again:
I spy with my little eye the following gems beginning with:
- O - Objects
- CF - Conditional Formats
- DV - Data Validation
- T - Text
- C - Constants
Of course there are many more and whether they are classified as gems to you will depend on your needs.
Text to Columns with an Excel Formula
Bao
very helpful! thanks!
Jun
It’s super super helpful~!
I’ve been googling more than 4 hours.
Nothing else worked.
Finally I got here~ T T.
Only this post worked perfect.
Drazen’s way is awesome~!
Thanks million~!
Catalin Bombea
Glad you like it Jun 🙂
You may want to try other ways to select and delete the rows where the result of the formula is a zero-length string returned by a formula (“”), which is not a blank, technically speaking.
You can use Ctrl-Find method: select desired range, press Ctrl+F, Find field must be empty; the important part is to use the Look In option, to look in Values, not in Formulas… Then press Alt+i (or Find All button); next step is to select all cells found by pressing Ctrl+A, all cells with zero-length string will be selected; you can quit the Find window, pressing Alt+F4, or by simply closing the find window. Right click on any selected cells to delete the rows and it’s all done…
Another nice way to remove the rows with zero-length strings is to use the Filter tool, from Data tab. Select the range, press the Filter button from Data tab; This filter will see the zero-length strings (or Null strings) as blanks, because the filter is looking by default in values, not in formulas. Filter for blanks, then simply select and delete those rows, remove the filter and you will have only non empty rows.
Now you have 3 functional methods to delete the zero-length strings from your data 🙂
Cheers,
Catalin
Jun
Thanks~!
I tried filter before and it worked fine.
I wondered if it keeps the order of rows after turning off the filter.
So I checked that out and found it kept well.
Using filter is also good good!. The only bad is it requires more clicks.(Filter On => Check
“Blanks” => Drag the Blank rows => Delete => Filter Off). It’ s kinda pain in the ass.
I think “Ctrl-Find method” is the best among 3 ways.(100% my opinion~ : ) )
Anyway thanks again your more tips.
And sorry about my poor English.(I’m not a native speaker).
Catalin Bombea
No worries, neither do i 🙂
Glad it works.
Cheers,
Catalin
Jef
Neat work and handy trick. Thanks for sharing.
Mynda Treacy
Thanks, Jef 🙂
sonu
Thanks for sharing Good trick…
Philip Treacy
Glad you found it useful Sonu.
Dave Bonin
I run into similar issues when working on imported .txt files. In short, I need to:
1) Import a large, tab-delimited text file of database data from another system.
2) Manually remove certain rows.
3) Write it back out as a tab-delimited text file.
Most of the cells that were blank are now filled with “” (pairs of double quotes).
In addition, all cells with text is now delimited on both ends by double quotes.
Yuck!
My solution for now:
4) Bring the text file into Word.
5) Replace all “^t with ^t
6) Replace all ^t” with ^t
7) Write the text file from Word.
Viola! I can fix 60,000 cells in just a minute or two. In addition, this method preserves
any intentional ” marks, such as might be used to indicate inches.
Mynda Treacy
Thanks for sharing, Dave.
That reminds me of editing I used to do in Word to remove carriage returns with a Find and Replace on ^p.
Mynda
Drazen
Hi Mynda,
I know for that problem for few years and there is also solution to this by using Find functionality which has no problem with this kind of “blanks”.
So, in Find dialog (Ctrl+F) leave Find what box empty, press Find All and then by pressing Ctrl+A select all results that were found. Then you can proceed as you described for deleting rows.
When you see founded results which are hyperlinks you can go to that part of window and also use keys like Ctrl, Shift, arrows, Home or End to select results that were found. You can also select all results with Shift+End or by pressing mouse while holding Shift key.
BTW you can sort results (A-Z or Z-A) by clicking on the headers of the columns in that window.
I hope this helps.
Drazen
Mynda Treacy
Great tips. Thanks for sharing, Drazen 🙂
Drazen
I am glad you like it 🙂
Jon Acampora
Nifty trick Mynda! Thanks for sharing. The F5 keys also opens the Go To menu, as an alternative to Ctrl+G. Ctrl+G is probably easier to remember though…
Mynda Treacy
Cheers, Jon.
Thanks for sharing the F5 key tip.
Mynda
MF
Hi Mynda,
Another trick is to use Auto Filter, which is smart enough to consider “” blank. Once blank is filtered, it can be deleted in the same way you described above.
btw, I am a big fan of Go To Special.
Cheers,
MF
Mynda Treacy
Hi MF.
Thanks for sharing another great option 🙂
Mynda
MF
My pleasure to share my two-cents worth on your site. 🙂
shafiq baig
i did not find the download option.how to download them
Mynda Treacy
Hi Shafiq,
There wasn’t a download workbook for this tutorial, sorry.
Kind regards,
Mynda