Extract multiple Keywords from a cell

[skpanda]

Hello All,

I was looking for a solution to a requirment I have and I came across below thread.

http://www.mrexcel.com/forum/excel-questions/893600-extract-multiple-keywords-text-string.html

I tried the suggestions given by you and other posters but it did not work for me. I wanted to request your help on this matter.

Kindly provide your inputs.

My Requirement:


1. Column A has Keywords. Example:
Cell A1: FIELD-NAME-1
Cell A2: FIELD-NAME-2
so on...
Cell A100: FIELD-NAME-100


2. Column W has free form text, that may or may not have keywords from Column A.

Cell W1: This is the free form text for FIELD-NAME-2
Cell W2: This is the free form text for FIELD-NAME-1 and FIELD-NAME-100.
Cell W3: This is the free form text for no fields.
So on..
Cell W20: This is the free form text for FIELD-NAME-1, FIELD-NAME-3, FIELD-NAME-10 and FIELD-NAME-50.


3. I want the below in Column X (Basically check the Keywords in Column A in each of the cells in Column W and give the list of keywords.

Cell X1: FIELD-NAME-2
Cell X2: FIELD-NAME-1, FIELD-NAME-100
Cell X3:
So on..
Cell X20: FIELD-NAME-1, FIELD-NAME-3, FIELD-NAME-10, FIELD-NAME-50.

Row Nbr	Col A	Col W	Col X
1	FIELD-NAME-1	This is the free form text for FIELD-NAME-2	FIELD-NAME-2
2	FIELD-NAME-2	This is the free form text for FIELD-NAME-1 and FIELD-NAME-100	FIELD-NAME-1, FIELD-NAME-100
3	FIELD-NAME-3	This is the free form text for no fields
So on..	so on..	so on..	So on..

<tbody>
</tbody>

Solution From the above post (Did not work):


a. Save excel sheet in .xlsm type. (I have excel 2010).

b. Inserted the below module in vba

Function Keywords(s As String, rWords As Range) As String
Dim kw As Object
Dim i As Long

With CreateObject("VBScript.RegExp")
.IgnoreCase = True
.Global = True
.Pattern = "\b(" & Join(Application.Transpose(rWords), "|") & ")\b"
If .test(s) Then
Set kw = .Execute(s)
For i = 1 To kw.Count
Keywords = Keywords & ", " & kw(i - 1)
Next i
End If
End With
Keywords = Mid(Keywords, 3)
End Function

c. In X1 Cell I put the below formula
=Keywords(W1,$A$1:$A$100)
X2 Cell I put the below formula
=Keywords(W2,$A$1:$A$100)
So on...
W20 Cell I put the below forumula
=Keywords(W20,$A$1:$A$100)

d. However the Cells in Column X show blank and not the expected result.

Not sure what I am doing wrong. Kindly help.

 

[Chrisdontm]

It looks like Column "A" has nothing to do with the answer, so try this in X1, and copy it on down...

=IFERROR(MID($W1,FIND("FIELD",$W1,1),100),"")