Friday, October 07, 2005

Finding Text in Excel


I had a request today for help. A client wanted to be able to look in a cell and based on if some words were found in that cell do something to another cell different from what he would have otherwise. To be specific. Column B had descriptions in it and column C a price. If a cell in column B had the words "No tag" in it then he wanted to add $25 to the price in C and the answer needed to be in D. Else the price should remain the same.

Here is the formula that I used:
=IF(ISERROR(FIND("No tag",B2,1))=TRUE,C2,C2+25)

This formula uses the FIND function to look for the text "No tag" in Column B. If it doesn't find it it returns a #VALUE! error. I used the ISERROR = TRUE to capture this instance, and the IF statement surrounding it all process TRUE to the error and does not change the price. If it does find the text then the error doesn't occur and the ISERROR statement returns FALSE and the formula adds $25 to the price.


Posted by: Jethro | 12:49 AM |