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

x
  • 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:

=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
Asked:
mytext
2 Solutions
 
nutschCommented:
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
 
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.
=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
 
mytextAuthor Commented:
Thank you
0

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