Hello,
I'm trying to find a formula for using the text string from one cell to search if that string is present in an other cell.
I need to make an overview of some software licenses, to see which one contains which options.
I hope you can help, as I have spent hours of trial and error.
Thanks
Here's one way.
Formula in B4 is:
The formula is filled right to F4, then down to row 9.
The highlight colour fill was applied manually to highlight the cells returning a correct result of the formula, but a wrong answer to your original questions.
A small revision to the data entry (with no change to the formula) had these results:
Hi Badunit,
Thanks for your reply.
Actually, I could have been more precise;
The long text string with all information also contains a unique code for all options;
"Options: 201 A; 202 B; 203 c; 301 Max, 302 One..." So would that be more useful to look for? Either the number code or the entire string of code and name, e.g. "203 c"
Will try today.
With the more precise search strings in column A, Badunit's original approach works for me.
=COUNTMATCHES(B$2,$A4)>0
Filled right.
If you need the matches to be case sensitive then just use REGEX within COUNTMATCHES.
=COUNTMATCHES(B$2,REGEX($A4))>0
SG
While it is simple to find if the letter "A" is in the string "A, B, C, max, one", there are nuances to it.
=COUNTMATCHES(B2,$A4)>0
This will look for the letter "A" anywhere in the string, case insensitive. There are two of them, the single letter "A" and the "a" in "max". Either one will return TRUE. This is probably not the answer you want.
=IFERROR(FIND($A4,B2)>0,FALSE)
This will look for "A", case sensitive. If cell B2 had "Alpha" as an option, it would return true even if there was not a standalone "A" in the list. Also probably not the answer you want.
=COUNTMATCHES(B2,REGEX("(^|, )"&$A4&"(, |$)",FALSE))>0
This complicated looking thing looks for "A" as being the entire string, "A, " at the beginning of the string, ", A, " somewhere in the middle, or ", A" at the end. It is case sensitive but can be made case sensitive. It requires each option in the list to all be followed by a comma and a space, except for the last one. An extraneous comma or misplaced or missing space could cause a problem. An option in the list can have a space in it like "max audio" and you can search for that term.
Thanks Barry,
Will try today.
There is actually more information in the string, which might make my search more efficient.
Please see reply to Badunit
Barry,
I believe if there are additional options such as "Max A" or "Alpha" or "A Max" or any others that have a capital A in them, a search for a standalone "A" will be TRUE when it shouldn't be.
With all the codes being 3 digits, it will be easy to look for the number in the list. Any of the formulas will work. I would choose the simplest.
=COUNTMATCHES(B2,LEFT($A4,3))>0
Thank you SO much for your assistance.
It works perfectly!
Search for a text string from one cell in another cell in Numbers
