"Clear" Worksheet Function?

[MattHyatt]

Cell A1 in my worksheet contains the string ="". Many (all?) functions that call on A1 return #VALUE errors. Is there a way to get the offending functions to treat the ="" the same as an empty (or zero) cell with another function, WITHOUT changing the contents of A1?

 

[phantom1975]

WELCOME TO THE BOARD!

You can try VALUE(A1).

 

[MattHyatt]

Thanks, I've tried VALUE(A1), but that also returns #VALUE.

 

[phantom1975]

If all else, you can try this:

if(A1="",0,A1)

The only other thing I can think of to tell you is to check the number formatting for A1.

 

[MattHyatt]

Ah, that did it. Sometimes I get so caught up in looking for complex answers I overlook the obvious. Thanks for getting me back on track.

 

[phantom1975]

It happens to all of us. Good luck.

 

[Mark W.]

On 2002-10-14 15:25, MattHyatt wrote:
Cell A1 in my worksheet contains the string ="". Many (all?) functions that call on A1 return #VALUE errors. Is there a way to get the offending functions to treat the ="" the same as an empty (or zero) cell with another function, WITHOUT changing the contents of A1?

Not all Excel worksheet functions will return #VALUE! (e.g., SUM) when they encounter "", but all arithmetic operators will. What functions are given you difficulties?