How do I put make a cell blank? 1

[mtn244]

How do I make a cell blank? For example, there is data in cells b1 through b100. If I want a blank in some of the cells based on some criteria, I have been following the steps below. However, the &quot;&quot; visually clears out the cell, but doesn't replace with blank. Doing an END-DOWN arrow from the top of the column doesn't result in the cursor stopping at the cell that is supposed to be blank and checking the cells with if(isblank(<address>) doesn't show the cells to be blank.

1. Insert column to the right of column B
2. Put formula in new column C: if(b1=&quot;N/A&quot;,&quot;&quot;,b1)
3. Copy formula down through C100
4. If step 3 works as expected, copy and Paste Special Values C1 - C100 over B1 - B100

I used to be able to actually blank out a cell in Lotus, but that was a long time ago and I don't remember the formula I used.

Thanks for any suggestions.

 

[SkipVought]

HI,

If you have a formula in the cell, then End-Down will NOT stop in that cell regardless of the value, including BLANK.

However, if you use your formula (except I woulf use ISNA) then the #N/A value will be replaced with a BLANK.

=if(ISNA(b1),"",b1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mtn244]

Thanks for your response. I didn't explain myself well, though. The &quot;N/A&quot; I was using was just example text, not the error message #N/A. My question is a generic how to clear out contents of cells if the value in the cells meet criteria specified in an IF() formula. Using &quot;&quot; in the formula makes the cells look blank, but result in FALSE when tested with isblank().

Here's an example:
Column B holds test data
Column C holds =IF(B1=&quot;N/A&quot;,&quot;&quot;,B1) - in C1 and copied down
Column D holds the contents of C1 - C8 copied and pasted special with values selected. This should result in blank cells D2, D5, and D6. That is what I expect anyway.
Column E hold the test =ISBLANK(D1) copied and pasted down the column.
Cell D9 holds =COUNTBLANK(D18)

B C D E
-----------------------------------------
1| a a a FALSE
2| N/A FALSE
3| c c c FALSE
4| d d d FALSE
5| N/A FALSE
6| N/A FALSE
7| e e e FALSE
8| f f f FALSE
9| 3

If COUNTBLANK() recognizes cells D2, D5, and D6 as blank, why doesn't isblank()?

I used an example instead of something specific because I often have need for clearing out cells conditionally. I do use &quot;&quot; in the IF() formula, but without the real results I hope for.

 

[Bowers74]

Hi mtn244,

The ISBLANK() function only recognises Null Values, that means no values whatsoever, to include Blank Values (i.e. &quot;&quot.

The COUNTBLANK() function recognizes both Blank and Null values.

When you copy/paste special.../values, all of the values that were in the copied cells are pasted into the destination cells (including blank values). That is why COUNTBLANK() recognizes the blank cells in column D in your example and ISBLANK() doesn't. ;-)

The only way I can think of actually &quot;clearing&quot; a cell due to the returned value of the cell's formula, would be to use VBA code. But, I doubt that this is the way you want to go. If it is then let us know!

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[mtn244]

Thanks, Mike!
Now that I know the difference in how countblank() and isblank() work, I won't continue to try to find a quick clear-the-cell method. Seems like there should be a way though, since I could do it through Lotus formulas.

If I knew how to give you a star, I would. Please give yourself a star from me for your explanation. I bet I'm not the only one who ever wondered about this.

Miki