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!!!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mytextAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.