Finding SEVERAL matches within a cell and return the group of words

[maxbuffat]

I have been looking at the following post: http://www.mrexcel.com/forum/showthread.php?t=547630

I used the following function:=LOOKUP(2^15,SEARCH($C$1:$C$3,A1),$C$1:$C$3) to look for key "group of words in a column" in a cell which contains text

e.g. i have in a cell "2 bedroom flat with kitchen" and in the column I have "flat", "kitchen", "2 bedroom"

The function help me to find ONE word but then it seems it doesn't return all the words. For example the function only return "kitchen" whereas there are 3 keywords present (kitchen, 2 bedroom & flat). It seems that when it found a word it doesn't go further.

<TABLE style="WIDTH: 616pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=821 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 189pt; mso-width-source: userset; mso-width-alt: 9216" span=2 width=252><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7972" width=218><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 189pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=252>A - Text in cell</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 189pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=252>B - Return of the function LOOKUP</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 26pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=35></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 164pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=218>C - List of workds I am looking for</TD></TR><TR style="HEIGHT: 60pt; mso-height-source: userset" height=80><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 60pt; BACKGROUND-COLOR: transparent" height=80>1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2 bedroom flat with kitchen, bathroom</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">kitchen</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2 bedroom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3 bedroom house</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">#N/A</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">flat</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2 bedroom house</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2 bedroom</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">kitchen</TD></TR></TBODY></TABLE>

I was wondering whether anyone knows how the results could return "flat; kitchen; 2 bedroom")

Thanks in advance,

 

[Jonmo1]

Welcome to the board..

You're probably going to need a VBA macro solution to get the multiple results in a single cell.

Is Macro solution acceptable?

 

[maxbuffat]

I am not very familiar with macro but if it is well explain and I can copy & paste, then that will be OK

 

[Jonmo1]

Press ALT + F11 to open the VBA editor.
Click Insert - Module
Paste the following code

Option Explicit
Function MySearch(MyStr As Variant, MyRange As Range)
Dim c As Range, x As Long, Rslt As String
For Each c In MyRange
    x = InStr(1, MyStr, c, vbTextCompare)
    If x > 0 Then
        Rslt = Rslt & c & ", "
    End If
Next c
If Rslt = "" Then
    MySearch = ""
Else
    MySearch = Left(Rslt, Len(Rslt) - 2)
End If
End Function

Close the VBA editor.

Now use a formula in your cell B1
=MySearch(A1,$C$1:$C$3)


Hope that helps.

 

[maxbuffat]

Thanks a lot.
I managed to create the macro but when I enter "=MySearch(A1,$C$1:$C$3)" in B1, it says there is "the formula you type contains error". It seems it doesn't like the comma.

For my functions, I usually use ";" instead of ",". Do you beleive it comes from this?

Thanks in advance

 

[Jonmo1]

yes, go ahead and use ; instead of ,

 

[maxbuffat]

I did but then in the cell I get "#NAME?#"

 

[Jonmo1]

Make sure you have macros enabled.

Which version of Excel do you have?

 

[maxbuffat]

Excel 2007
it was saved as xls. I did save as xlsx but did not change anything

 

[Jonmo1]

You also have to enable macros from
Excel Options - Trust Center - Trust Center Settings - Macro Settings

Also put a check on "Trust access to the VBA project object model"



Are you sure you've copied the code and formula exactly as posted?
And you pasted the code into a new module (Click Insert - Module)