Find a string of text within a string

[rs1aj]

I know its simple but I can't get it to work right.

=IF(ISNUMBER(SEARCH(OR("bill","Credit"),[@Descriptn])),"BillBack",[@[Our Ref Trim]])

If the Descriptn has "bill" or "Credit" anywhere with the text string, I want to return "BillBack", if not, return the value of "Our Ref Trim"

So, I entered the formula above but it is not catching descriptions that contain "billback", "bill ", "credit"

Please help.

PS. "Our Ref Trim" is the helper column I use to =Trim() the "Our Ref" column. I'd like to get rid of the "Our Ref Trim" column and just add the "Trim" function within the formula above.

Thanks!

[MarvinP]

Hi rs1aj,

Try putting "*" in you search and see what happens.
Change the
OR("bill","Credit")
to
OR("*bill*","*Credit*")

[Glenn Kennedy]

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"credit","bill"},A1)))))>0,1,"") will do the job. wrap that up in your isnumber function and the rest of your formula and it'll work perfectly.

[rs1aj]

Works perfect. Thanks!

[Glenn Kennedy]

You're welcome.