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>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top