How to find a range of words in a list of row?

rj_081

New Member
Joined
Sep 23, 2019
Messages
4
Hello All,
Here is the sample strings in (A2:A11)result(B2:B11)
Lorem ipsum dolor sit amet, amber amber consectetur beige adipiscing elit.amber|beige
Nunc azure dictum black odio light blue quis nunc imperdiet viverra.black|light blue|blue
Sed at beige beige beige amber urna beige et bronze massa porttitor molestie.beige|amber|bronze
Phasellus ut massa non urna eleifend ornare vel sed turpis.
Mauris volutpat sem blue et faucibus convallis.blue
Curabitur non risus quis arcu hendrerit luctus eget vel dui.
Etiam non libero consequat, laoreet quam sit amet, ornare dolor.
Phasellus nec black ipsum bibendum, fringilla sem non, vehicula metus.black
Morbi a lacus black pulvinar, cursus nisi at, vulputate sapien.black
Curabitur tincidunt elit id ultricies fermentumbronze azure elit id.azure(only exact match)
and i have range of words in (E17:E23), in this case colors but can be a number,text, alphanumeric digit as well
amber
azure
beige
black
light blue
bronze
blue
I m looking for a solution either it be any formula, function or macros.
I have range of words which i need to find in the list of strings to obtain the desired result, hopefully the above data could help.
any help would be highly appreciated.
Thanks

<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Try this Function

Code:
Function range_of_words(a As String, b As Range)
  Dim c As Range, cad As String
  For Each c In b
    If InStr(1, a, c) > 0 Then cad = cad & c & "|"
  Next
  If cad <> "" Then range_of_words = Left(cad, Len(cad) - 1) Else range_of_words = ""
End Function
use:

=range_of_words(A2,$E$17:$E$23)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
If you have the TEXTJOIN function, try this in B2:

=TEXTJOIN("|",TRUE,IF(ISNUMBER(SEARCH(" "&$E$17:$E$23&" "," "&A2&" ")),$E$17:$E$23,""))

confirmed with Control+Shift+Enter.

It also looks like your A3 value should also have azure. Also, when you say on A11 azure is the only exact match, I assume you mean not to count bronze because it's part of fermentumbronze. In which case, Dante you'll need to tweak your code a bit.
 

rj_081

New Member
Joined
Sep 23, 2019
Messages
4
Try this Function

Code:
Function range_of_words(a As String, b As Range)
  Dim c As Range, cad As String
  For Each c In b
    If InStr(1, a, c) > 0 Then cad = cad & c & "|"
  Next
  If cad <> "" Then range_of_words = Left(cad, Len(cad) - 1) Else range_of_words = ""
End Function
use:

=range_of_words(A2,$E$17:$E$23)
Hi Dante,

This is actually working as per my need, but can you do a little modfication in the code.
As in cell A11 i have words "fermentumbronze" "azure" and i want an exact match so my desired result is only "azure" but i m getting "bronze|azure" with this code hopefully you under want need to say.

Thanks for your time, i really appreciate that.

Rajat
 

rj_081

New Member
Joined
Sep 23, 2019
Messages
4
If you have the TEXTJOIN function, try this in B2:

=TEXTJOIN("|",TRUE,IF(ISNUMBER(SEARCH(" "&$E$17:$E$23&" "," "&A2&" ")),$E$17:$E$23,""))

confirmed with Control+Shift+Enter.

It also looks like your A3 value should also have azure. Also, when you say on A11 azure is the only exact match, I assume you mean not to count bronze because it's part of fermentumbronze. In which case, Dante you'll need to tweak your code a bit.
Hi Eric,

Sorry, But i dont have TEXTJOIN Funtion can you please help me with this.

Thanking you in advance
Rajat
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Thanks Eric for the comments.

I put the updated macro, also considering that the word may exist at the end of the sentence, followed by the period "."

Erick, I don't have the TEXTJOIN function, but I guess you should also consider a case like this:
Code:
Morbi a lacus [COLOR=#ff0000]black [/COLOR]pulvinar, cursus nisi at, vulputate sapien [COLOR=#ff0000]blue[/COLOR].

Code:
Function range_of_words(a As String, b As Range)
  Dim c As Range, cad As String
  For Each c In b
    If InStr(1, " " & Replace(a, ".", " ") & " ", " " & c & " ") > 0 Then cad = cad & c & "|"
  Next
  If cad <> "" Then range_of_words = Left(cad, Len(cad) - 1) Else range_of_words = ""
End Function
-------------------------------------

★ rj_081, I have doubt in this case:


Nunc azure dictum black odio light blue quis nunc imperdiet viverra.
The result must be:

azure|black|light blue|blue



Or:


azure|black|light blue
 

rj_081

New Member
Joined
Sep 23, 2019
Messages
4
Thanks Eric for the comments.

I put the updated macro, also considering that the word may exist at the end of the sentence, followed by the period "."

Erick, I don't have the TEXTJOIN function, but I guess you should also consider a case like this:
Code:
Morbi a lacus [COLOR=#ff0000]black [/COLOR]pulvinar, cursus nisi at, vulputate sapien [COLOR=#ff0000]blue[/COLOR].

Code:
Function range_of_words(a As String, b As Range)
  Dim c As Range, cad As String
  For Each c In b
    If InStr(1, " " & Replace(a, ".", " ") & " ", " " & c & " ") > 0 Then cad = cad & c & "|"
  Next
  If cad <> "" Then range_of_words = Left(cad, Len(cad) - 1) Else range_of_words = ""
End Function
-------------------------------------

★ rj_081, I have doubt in this case:




The result must be:

azure|black|light blue|blue



Or:


azure|black|light blue
Wow thanks for such a fast solution. This one is what i was looking for, I will test this code with different sets of data and update the status as solved ones i find this convincing.

yes the output should be "azure|black|light blue|blue" i accidentally forgot to write azure.

Thanks again
Rajat
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Okay, let me know if you have any questions.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
The OP doesn't have TEXTJOIN, but for future visitors, here's the modified formula which addresses Dante's point about the period:

=TEXTJOIN("|",TRUE,IF(ISNUMBER(SEARCH(" "&$E$17:$E$23&" "," "&SUBSTITUTE(A2,".","")&" ")),$E$17:$E$23,""))

with CSE.
 
Last edited:

Forum statistics

Threads
1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top