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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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:


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!!!!!
2 Solutions
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.

Ejgil HedegaardCommented:
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.
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
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.
mytextAuthor Commented:
Thank you

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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