This is more complex (at least to my knowledge) because it cannot simply OR all of the results of the array into a single outcome. B1 contains "Abc Xyz" and the list contains a semi-random list of names with "Ab", "Xy, "Abc", etc., as well as blanks. I expanded the test set to 16 to evaluate different scenarios. Seeing JB's answer creates more doubt, but here goes: =OR(ISNUMBER(FIND(TRANSPOSE(FILTERXML(""&SUBSTITUTE(B1," ","")&"","//sub")), I may not have fully understood the question, so apologies in advance if this is off the mark. (Edit: In a nutshell, I'm looking for a way to search against whole-word matches only, with case sensitivity, from a list of search terms.) Been wracking my brain on this for hours. The MATCH function doesn't work because "Abc Xyz" will never be in the A1:A10 list in a single cell.Īny help you all can provide is GREATLY appreciated.The EXACT function doesn't work because "Abc" and "Xyz" are not exact matches to "Abc Xyz".I might have the syntax wrong, but the search-for range would be A1:A10&"~ ". BUT it allows me to use wildcards, so I can search "Ab~ " so it looks for a space only after the search term, which gives me the "Ab" false when I need it. The SEARCH function isn't Case Sensitive, so it finds "Bc", so I get a true when I want a false.And it can't use wildcards, see next comment. So with "Ab" I get a true when I want a false. The FIND function is Case sensitive, so it won't register a match with "Bc" which is good, but it will with "Ab".But if "Abc" or "Xyz" was added to the list, then the search would return true because a whole word was found. I want this search to return false, because a whole word from the list wasn't found in B1. Within the A1:A10 list, there are the strings "Ab" and "Bc".
Lets say cell B1 contains the string "Abc Xyz". Here is a random example to explain my logic: I want to search cell B1 for any of the words in the A1:A10 list, BUT I need it to search WITH Case Sensitivity, AND ALSO allow the use of wildcards (so I can get whole word matches only). So, I have a list of cells that each contain a word, lets say in cells A1:A10.
I can find bits and pieces of different formulas online but haven't been able to get what I need.
The trick now consists in replacing the direct reference to $G$1 by the EVALUATE-augmented named range search_terms.I've been struggling to make this work. To be used as input to the SEARCH function: using a direct reference to the SINGLE cell $G$1 (augmented with the curly braces in that case) inside SEARCH does not work, hence the use of named ranges and EVALUATE. In the Refers to: field I put the following: =EVALUATE(" Then, I defined a named range named search_terms for that cell as described in the link above and shown in the figure below: In addition to the answer of I would like to add that you can put the string of multiple search terms inside a SINGLE cell (as opposed to using a different cell for each term and then using that range as argument to SEARCH), using named ranges and the EVALUATE function as I found from this link.įor example, I put the following terms as text in a cell, $G$1: "PRB", "utilization", "alignment", "spectrum"
0 kommentar(er)
