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

[rj_081]

[TABLE="width: 665"]
<tbody>[TR]
[TD]Hello All,[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Here is the sample strings in (A2:A11)[/TD]
[TD]result(B2:B11)
[/TD]
[/TR]
[TR]
[TD]Lorem ipsum dolor sit amet, amber amber consectetur beige adipiscing elit.[/TD]
[TD]amber|beige[/TD]
[/TR]
[TR]
[TD]Nunc azure dictum black odio light blue quis nunc imperdiet viverra.[/TD]
[TD]black|light blue|blue[/TD]
[/TR]
[TR]
[TD]Sed at beige beige beige amber urna beige et bronze massa porttitor molestie.[/TD]
[TD]beige|amber|bronze[/TD]
[/TR]
[TR]
[TD]Phasellus ut massa non urna eleifend ornare vel sed turpis.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mauris volutpat sem blue et faucibus convallis.[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]Curabitur non risus quis arcu hendrerit luctus eget vel dui.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etiam non libero consequat, laoreet quam sit amet, ornare dolor.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phasellus nec black ipsum bibendum, fringilla sem non, vehicula metus.[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]Morbi a lacus black pulvinar, cursus nisi at, vulputate sapien.[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]Curabitur tincidunt elit id ultricies fermentumbronze azure elit id.[/TD]
[TD]azure(only exact match)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]and i have range of words in (E17:E23), in this case colors but can be a number,text, alphanumeric digit as well[/TD]
[/TR]
[TR]
[TD]amber[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]azure[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]beige[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]light blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bronze[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I m looking for a solution either it be any formula, function or macros.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]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.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]any help would be highly appreciated.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this Function

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]

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]

Try this Function

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]

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]

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:

Morbi a lacus [COLOR=#ff0000]black [/COLOR]pulvinar, cursus nisi at, vulputate sapien [COLOR=#ff0000]blue[/COLOR].

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]

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:

Morbi a lacus [COLOR=#ff0000]black [/COLOR]pulvinar, cursus nisi at, vulputate sapien [COLOR=#ff0000]blue[/COLOR].

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]

Okay, let me know if you have any questions.

 

[Eric W]

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.