Thread: [Solved] Help with LEN and Substitute function
View Single Post
 
Old 11-08-2014, 05:28 AM
whatsup whatsup is offline Windows 7 64bit Office 2010 32bit
Competent Performer
  
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default
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!!!
Reply With Quote