Thomas

Solved

Posted on 2014-07-16

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!!!!!

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",UPPE

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

But when I replace “0” with 0%

=IF(ISERROR(FIND("0%",UPPE

Has anyone any ideas?

Many thanks!!!!!

3 Comments

Thomas

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)),

or

=IF(ISERROR(FIND(0%,W1)),0

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))

will find it and return K1.

Unfortunately =IF(ISERROR(FIND("0",W1)),

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Opening Files From Excel WB | 8 | 30 | |

"Microsoft Excel has stopped working" error when getting external data from Access | 10 | 24 | |

Simple Calculation for Value of Availablity | 5 | 48 | |

Excel VBA, find a string in a column, update a cell | 7 | 23 |

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

Connect with top rated Experts

**11** Experts available now in Live!