Excel - Large List with keywords. Search in a cell to see if contains keyword and if yes return keyword that is found.

witedwarf

New Member
Joined
Apr 16, 2018
Messages
3
I have a range of Keywords (named range Devices) its a somewhat large list 586 lines (it will grow over time).

I have on another worksheet a large amount of data will grow to about 100,000+ lines by end of year.

I want to search to see if cell A2 in this worksheet contains one of the keywords in my "Devices" list and if it does contain the item in this list then return the vaule of the list.

Can someone help the closest I have came is a true/false or Yes/No result with an Array as in below


{=IF(OR(COUNTIF(A2,"*"&DEVICES&"*")), "Yes", "no")}

This returned yes as my list was found in A2 but I want what it actually found in my list

Thank you all for your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Some clarifying questions:
1. Is it possible that 2 or more of the keywords appear in a single cell? If so, what should the result be? (examples?)
2. If one of the keywords is "king" and the text in the cell being checked is "The kingdom of Nepal", is that a match? (part word)
3. If one of the keywords is "king" and the text in the cell being checked is "The King is dead", is that a match? (case match would fail but text match would pass)
4. If the answer to 2 is "no", only looking for whole words, then does (or could) the cell being checked contain punctuation like this "The king's clothes are expensive" or "Where is the king?"? Punctuation can make checking for whole words tricky.
 

witedwarf

New Member
Joined
Apr 16, 2018
Messages
3
1. it is possible but would be a limited result, the result could be something of the sort as "Multiple matches" and I would have to review these results.
2. yes that is a match for example I have "server123" in my "Devices" list and if server123.mydomain.com in the cell I am searching I want "server123" from my displayed.
3. Yes that is a match, as the casing is irrelevant using example I have "server123" in my "Devices" list and in the cells I am searching "Server123" I want "server123" from my list displayed.
4. I do not have any punctuation in my "Devices" list to search for and it is not required.

Thank you for your help with this! I really appreciate all assistance with this.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
2. yes that is a match for example I have "server123" in my "Devices" list and if server123.mydomain.com in the cell I am searching I want "server123" from my displayed.
Does that one present a problem? Could you have "server 12" and "server123" in your devices list? If so, both of those would match in "server123.mydomain.com". Is that possible with your data?

And so if "server123" was NOT in your devices list but "server12" was and the cell to be checked has "server123.mydomain.com" then your answer to Q2 says that is a match. Is that correct?

I do not have any punctuation in my "Devices" list to search for and it is not required.
But you do have punctuation in the cell(s) to be checked (server123.mydomain.com) which is what I had asked about. :)


Do you care if the suggestion is a formula or uses vba?
 
Last edited:

witedwarf

New Member
Joined
Apr 16, 2018
Messages
3
Does that one present a problem? Could you have "server 12" and "server123" in your devices list? If so, both of those would match in "server123.mydomain.com". Is that possible with your data?

And so if "server123" was NOT in your devices list but "server12" was and the cell to be checked has "server123.mydomain.com" then your answer to Q2 says that is a match. Is that correct?
Yeah based of the logic it would but I do not want that to occur (if possible) so I want server12 only to locate server12. So for example I have l00123 and L00123AP I want l00123ap to be returned and not l00123 as there is a big difference. I am going to try and clean my list to be more exact and not too general but there is a distinct possibility of this to occur.


But you do have punctuation in the cell(s) to be checked (server123.mydomain.com) which is what I had asked about. :)


Do you care if the suggestion is a formula or uses vba?
Ah yes I do have punctuation. Sorry about that.

I am not very versed with VBA but I can attempt to do it. As I am assuming this is not going to be possible with a formula based off of some of the complexity I keep adding.
 

Forum statistics

Threads
1,082,507
Messages
5,365,978
Members
400,863
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top