Log In
Register
Home
Active Member
February 22, 2023
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
The following users say thank you to Riny van Eekelen for this useful post:
Anders SehlstedtActive Member
February 22, 2023
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
You could use something like:
=LET(counts,SEARCH(names,A2),TEXTJOIN(",",TRUE,CHOOSECOLS(SORT(FILTER(HSTACK(names,counts),ISNUMBER(counts)),2),1)))
Active Member
February 22, 2023
Offline
Thanks Velouria, it worked.
Active Member
March 5, 2023
Offline
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
1 Guest(s)
