[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 236

# Problem with iserror find Formula

I am trying to get excel to do the following.

If the contents of one cell W contains 23% return the contents of another cell K if not return 0.  I was able to reuse an old formula and came up with:

=IF(ISERROR(FIND("23",UPPER(W1))),0,K1)

This works even if the number is 23 or 23 %.

But when I try adapting the formula as below to search for 0
=IF(ISERROR(FIND("0",UPPER(W1))),0,K1) it always returns K

But when I replace “0” with 0%
=IF(ISERROR(FIND("0%",UPPER(W1))),0,K1)  it always returns the fall back zero.

Has anyone any ideas?

Many thanks!!!!!
0
mytext
2 Solutions

Commented:
That formula should work just fine if the cell W1 contains 0% as text. If it contains 0% as a number then, the formula will not work. Remove any alignment formatting from your cell. If the 0% is to the right of the cell, it's a number, on the left, it's text.

Thomas
0

Commented:
If the value in W1 is a number, then 23% is actually 0.23, so the the Find function, looking for "23" returns 3 because "23" starts at position 3.
If W1 is 0%, the value in the cell is 0, so Find "0%" returns an error, where Find "0" returns 1.
The percentage sign is a format to show, not a part of the cell value.
If you want to look for 23% or 0%, make the search directly with the value.
=IF(ISERROR(FIND(23%,W1)),0,K1)
or
=IF(ISERROR(FIND(0%,W1)),0,K1)
You don't have to use the Upper function with numbers.
For text, use Search instead of Find, Search is not case sensitive.

But if the real value is 23.001 = 0.23001, just shown as 23% by formatting, the text match
=IF(ISERROR(FIND("23",W1)),0,K1)
will find it and return K1.
Unfortunately =IF(ISERROR(FIND("0",W1)),0,K1) will also return K1, since the zeros after 23 will match.
0

Author Commented:
Thank you
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.