Finding partial text in cells (could be conditional formatting or formula)

Hi,

I think it should be pretty easy, but I can't figure out a quick way of doing this. I want to find any matches (variations) of a text string in a data set.

In the attached spreadsheet, there is no consistency in the Item names. I'm looking to find all the possible box names. If it has any combinations of "A-Box", "A Box", "abox", etc. or any other way that "a box" can be written, I want to know about it (in the spreadsheet, A2, A4, A6, A8, A10 would match that criteria). This could be done through Conditional Formatting of the Item column or through a lookup formula (I'm open to any suggestions).

Please let me know of any your thoughts. Thank you in advance for your help,

Yury

Hello
Yes you can do both the ways
with formulas type in
=IF(IFERROR(IFERROR(IFERROR(SEARCH("abox",A2),SEARCH("A-Box",A2)),SEARCH("A box",A2)),0)<>0,"Found","Not-found")
This formula is non-case-sensitive

You can further exapnd the formula for other criterias

if it returns "found" the value is found.....

For conditional formatting
use this formula
=IFERROR(IFERROR(IFERROR(SEARCH("abox",A2),SEARCH("A-Box",A2)),SEARCH("A box",A2)),0)<>0
select your format


Tell me if it works right….

Hope it helps
Do ask for any other query you may have…

If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
Regards
Sourabh Gupta

Hi bebo021999,

Thank you very much for the formula. I want to make sure that I understand it, so that I can use it for other instances (with other text). I have a couple of questions:
1) What is the "/" in your formula? I tried to search for this online, but couldn't find anything.
2) I'm assuming that if there are other instances of "variations" of "abox", then I can just continue nesting the SUBSTITUTE formula of the all the different "variations". Is that correct?
3) I'm assuming that this formula would also find an instance of "kindofabox", but not "the box". Is that correct?

Thank you again. Please let me know if you can. All the best,

Yury

Hi Sourabh,

Thank you very much for your explanation. I want to make sure that I understand your formula exactly so that I can use it in other instances (using other text). I have a couple of clarifying questions:

1) I'm assuming that if there are other instances of "variations" of "abox", then I can just continue nesting the IFERROR/SEARCH formulas of the all the different "variations". Is that correct?
2) What is the reason for "<>0" (i.e. "it does not equals zero") at the end of your formula?
3) If there are different types of separators within "abox", like "a-box", "a box", "a.box", "a*box", etc., can I use "a?box" in the formula instead, using the "?" as "any character" in the formula?

Please let me know if you have a chance. Thank you in advance,

Yury

Originally Posted by bebo021999

1) Yes I assume that variation of "a/box"

Thank you for the answers. For Answer 1, how does the formula know to add the "/" after the first character (i.e. to substitute the second character in the text string, not any other one". In other words, I understand that you're substituting the "/" with "" and other characters with "", but I can't see why the formula would substitute something between the first and second character (not between the second and third or third and forth).

I'm sorry to not be getting this, but I want to make sure that I understand your logic to help me with other instances.

Thank you again, Yury

So should the formula be...

=ISNUMBER(SEARCH("abox",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"a/box","abox"),"-","")," ",""))) OR Something else?

Thank you for your help, Yury