Problem with iserror find Formula

Posted on 2014-07-16
Last Modified: 2014-09-06
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!!!!!
Question by:mytext
    LVL 39

    Assisted Solution

    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.

    LVL 20

    Accepted Solution

    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.

    Author Closing Comment

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now