Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is common in Excel to use "" to give a cell a blank value, as in =IF(ISNA(A1),"",A1). The problem is that the cell looks empty, but it isn't truly empty. The COUNTBLANK function will count it as a blank cell, but if you test it with ISBLANK it comes up false. Is there any way to substitute something other than "" to make the cell truly empty?

  2. #2
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Please refered to this Thread #12, regarding blank cell and formula blank cell

    Blank Excel Cells

    Regards
    Bosco

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Bill Wright View Post
    Is there any way to substitute something other than "" to make the cell truly empty?
    No, there is no way to make a formula return a "completely blank" value; the empty string "" is the best you can do.
    Instead of testing the result with ISBLANK(cell), you can use cell="". This will catch both really blank cells and cells that contain an empty string.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    No, there is no way to make a formula return a "completely blank" value; the empty string "" is the best you can do.
    Instead of testing the result with ISBLANK(cell), you can use cell="". This will catch both really blank cells and cells that contain an empty string.
    Thanks, Hans. I expected that was the case, but I thought maybe I had missed something. It would be nice if they added a function to Excel to completely zap a cell (like Clear > All from the Edit menu). Unitl they do that, I'll just have to do as you recommend and avoid ISBLANK.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think Microsoft will do that. A cell containing a formula is not blank, even if the formula returns an empty string.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What I have done on occasion is to have the cell give an error [like na()] rather than the null. Then used edit- goto formulas and check just errors and onece they are selected you can hit <del> to clear them all.

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    What I have done on occasion is to have the cell give an error [like na()] rather than the null. Then used edit- goto formulas and check just errors and onece they are selected you can hit <del> to clear them all.

    Steve
    Excellent idea, Steve. I hadn't thought about that. It actually makes it easier because I don't have to check for #N/A - I can just leave them and zap those cells with your procedure.

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cell zapping is done with the .ClearContents method, as in: Selection.ClearContents.

    --Scott.

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Scott McNay View Post
    Cell zapping is done with the .ClearContents method, as in: Selection.ClearContents.

    --Scott.
    Scott,
    You're right, but in this case I wasn't going to use VBA. Just formulas and manual clearing.

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •