• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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
September 1, 2014 by Mynda Treacy

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

top excel functions for data analysts

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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 and 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

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

Excel VSTACK and HSTACK Functions

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

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.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Text to Columns with an Excel Formula
Next Post:Excel Pivot Tables Year on Year Change

Reader Interactions

Comments

  1. Bao

    November 5, 2020 at 9:21 pm

    very helpful! thanks!

    Reply
  2. Jun

    April 1, 2015 at 10:08 am

    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

      April 1, 2015 at 4:34 pm

      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

        April 1, 2015 at 7:09 pm

        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
        • Catalin Bombea

          April 2, 2015 at 12:56 am

          No worries, neither do i πŸ™‚
          Glad it works.
          Cheers,
          Catalin

          Reply
  3. Jef

    September 9, 2014 at 3:49 pm

    Neat work and handy trick. Thanks for sharing.

    Reply
    • Mynda Treacy

      September 9, 2014 at 3:57 pm

      Thanks, Jef πŸ™‚

      Reply
  4. sonu

    September 5, 2014 at 2:53 am

    Thanks for sharing Good trick…

    Reply
    • Philip Treacy

      September 5, 2014 at 10:26 am

      Glad you found it useful Sonu.

      Reply
  5. Dave Bonin

    September 4, 2014 at 12:36 am

    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

      September 4, 2014 at 8:40 am

      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

    September 3, 2014 at 5:02 pm

    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
    • Mynda Treacy

      September 3, 2014 at 9:35 pm

      Great tips. Thanks for sharing, Drazen πŸ™‚

      Reply
      • Drazen

        September 4, 2014 at 2:51 am

        I am glad you like it πŸ™‚

        Reply
  7. Jon Acampora

    September 3, 2014 at 9:34 am

    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
    • Mynda Treacy

      September 3, 2014 at 11:25 am

      Cheers, Jon.

      Thanks for sharing the F5 key tip.

      Mynda

      Reply
  8. MF

    September 2, 2014 at 12:22 pm

    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
    • Mynda Treacy

      September 2, 2014 at 1:30 pm

      Hi MF.

      Thanks for sharing another great option πŸ™‚

      Mynda

      Reply
      • MF

        September 2, 2014 at 2:56 pm

        My pleasure to share my two-cents worth on your site. πŸ™‚

        Reply
        • shafiq baig

          February 20, 2015 at 10:11 pm

          i did not find the download option.how to download them

          Reply
          • Mynda Treacy

            February 20, 2015 at 10:16 pm

            Hi Shafiq,

            There wasn’t a download workbook for this tutorial, sorry.

            Kind regards,

            Mynda

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.