Clearing Cells in a Formula

[goodhart]

Is there anyway to make a cell blank using a formula. For example if there is text in the cell clear it? I've tried if(istext(A2),"",A2) but the "" is not really an empty cell, it is text (ISTEXT() returns true and ISBLANK() returns false).

I can do this with a macro (I think it is with .ClearContents(), I but want to do it in a formula.

Thanks,

Curt

 

[Greg Truby]

Welcome to the board.

Yes, it's Range(x).ClearContents And I cannot come up with a way to do this. Everything returns either a text value or a numeric or something - nothing that would get an IsBlank() to come back true. Even tried putting a reference to a blank cell as the true condition and got a zero, not a blank back. Unless one of the MVP's knows of a trick I've not seen, you're looking at VB.

 

[DRJ]

A formula has to return a value. If you really need to clearcontents a cell you need vba.

 

[IML]

you can also cheat a little and evaluate with
=LEN(A1)=0
rather than
=ISBLANK(A1)

 

[IML]

you can also cheat a little and evaluate with
=LEN(A1)=0
rather than
=ISBLANK(A1)

I'm sorry I totally misread your question. If I understand you correctly no, you could try the following
hit control G
select special
select constants
select okay

If this range looks like what you want to delete, do so.

Otherwise, a VBA could be applied as previously suggested.