Hi, I saw Search a text string for a list of words but this did not solve my problem in excel for M365.
I have a column A with Strings and would like to return all matching words against a List in Column B.
For example,
My list is {"Bob","John","Smith"}
Cell A2 has: Bob and John went to dinner. I want B2 as Bob, John
Cell A3 has: John, Bob and Smith went to dinner: I want B3 as John, Bob, Smith
Cell A4 has: Smith went alone for dinner. I want B4 as Smith
If this is not possible, at least the first matching name from the sentence has to be return. So B2 will be Bob, B3 as John and B4 as Smith.
From the Blog, the ROW multiplication is not working and it is always returning sum.
In MS365, that would be:
=TEXTJOIN(", ",,FILTER(names,ISNUMBER(SEARCH(names,A2))))
where "names" is a named range containing the list of names.
The attached file contains an example.
Hi Rinv,
Thanks for the reply. The formula worked, thanks.
How to get the first matched word first rather than first in the array,
for example, B3 should be John, Bob, Smith as John is first match.
You could use something like:
=LET(counts,SEARCH(names,A2),TEXTJOIN(",",TRUE,CHOOSECOLS(SORT(FILTER(HSTACK(names,counts),ISNUMBER(counts)),2),1)))
Thanks Velouria, it worked.
Yessssss!
This works great for my problem.
Just needs a little more fine tuning.
The only part I need is this.
=FILTER(MonthResults,ISNUMBER(SEARCH(Months,A16)))
I am using this data (see Attachment ) and need to find exact match. Sometimes an abbreviation or code may be used in the field being searched. With the formula as is it is finding partial matches which is causing issues. I tried quotes around the named range etc. Nothing worked.
Thanks~
Mike G
Hello,
Problem solved. Found on another forum.
This was the code that worked for me.
=INDEX(SizeR,MATCH(TRUE,ISNUMBER(SEARCH(Size,A2)),0))
Thanks~
Mike G
