My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Deleting Rows in Excel Containing Blanks Returned by Double Quotes

You are here: Home / Excel Formulas / Deleting Rows in Excel Containing Blanks Returned by Double Quotes
by

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!

The "" is like an invisble stain on your spreadsheet that no amount of scrubbing will remove.

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:

example of N/A returned by function

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:

  1. Select the cells C1:C11
  2. Press CTRL+G to open the Go To dialog box
  3. Click the ‘Special’ button, Note: it’s both special and called ‘Special’ 🙂
  4. Select ‘Formulas’ and ‘Errors’ as shown below then click ok.

go to special dialog box

Now all the cells containing errors are selected:

rows containing errors selected

And you can right-click > Delete > Delete Entire Row:

delete rows dialog box

So now you’re left with the clean data:

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:

go to special dialog box

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.

More Excel Formulas Posts

excel formula by example
Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category:

Reader Interactions

Comments

  2. 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~!

    Reply

    • 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

      Reply

      • 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).

        Reply

  5. 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.

    Reply

    • 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

      Reply

  6. 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

    Reply

  7. 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…

    Reply

  8. 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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *