Clearing Cells in a Formula

goodhart

New Member
Joined
Mar 10, 2004
Messages
1
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
A formula has to return a value. If you really need to clearcontents a cell you need vba.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
you can also cheat a little and evaluate with
=LEN(A1)=0
rather than
=ISBLANK(A1)
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
IML said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top