Help with LEN and Substitute function

whatsup · #5 11-08-2014, 05:28 AM

Quote:

...as long as I do not add a number and then delete

Now that seems to be a problem the way you delete the contains of a cell. Sticking to F20, if
= LEN(F20)
results in zero, then the formula will work correctly, if you insert for example <space> then it won't. Though the cell appears empty it isn't, and above formula will result in 1.

As explanation, with
OR(F20="",F20=0,F20="0")
you check three cases for F20:
- "" = the cell is empty or (as a result of a formula) contains an empty string
- 0 = the cell contains a 0 as number
- "0" = the cell contains a 0 as text (string)
in either case, the formula will return a fixed result of zero!!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
#REF! Error in calling VBA function disappears when function is copied	lcaretto	Excel Programming	2	05-26-2014 07:19 PM
Creating a graph for Future Value function (FV function)	bmoody	Excel	2	11-06-2013 10:52 AM
Find & Replace: substitute red-coloured words with underscores	tinfanide	Word	2	10-06-2012 11:04 PM
how to use now() function	gsrikanth	Excel	1	01-07-2012 01:18 AM
if function help	jim831	Excel	2	10-29-2010 07:06 PM