Function Evaluation not what I want

[Bruce Roberson]

The function evaluates some values exported from quicken, attempting to
extract the character "S" from a value as shown below in the find part of
the function. It successfully does that after it finds the position of the
"S" character. However, if the cell entry in column D is a blank field, then
it puts in the value 0 instead of leaving it blank. Why does it do that?
Isn't there an option to make it not evaulate a blank to zero?


=IF(ISERR(LEFT(D11,FIND("S",D11,1)-1)),D11,LEFT(D11,FIND("S",D11,1)-1))

Thanks,


Bruce

 

[JE McGimpsey]

It returns 0 for the same reason that entering =D11 returns 0 when D11
is blank - by default, XL assumes a numeric value will be returned by a
formula.

One way:

=IF(D11="","",IF(ISERR(FIND("S",D11)),D11,LEFT(D11,FIND("S",D11)-1)))