PDA

View Full Version : Solved: Extract Key Words



vishwakarma
10-15-2010, 03:44 AM
Hello Guys,

Is there any way by which we can extract the key words from a given string? Any UDF or excel function?...

For Example :-
Malaysian Association of Convention and Exhibition Organisers and Suppliers :- Extract "Convention and Exhibition Organisers and Suppliers"
European Regional Science Association :- Extract "Regional Science"
International Society of Aesthetic Plastic Surgery :- Extract "Society of Aesthetic Plastic Surgery"I've attached a sample file with some sample data along with the desired result...


Thanks,


Manoj

Kenneth Hobs
10-15-2010, 06:02 AM
We would have to know the criteria that defines a key word. You may need to post a list of key words or list on non-key words.

vishwakarma
10-17-2010, 10:35 PM
Hi Kenneth,

I want the below mentioned words to be excluded from the string...

Association,Federation,Club,Guild,Union,Council,Society,Chamber,Chambers,Ad ministrators,Council,American Academy,National Academy,Professionals,Institute,Agents,Forum,Agencies,Agency,Alliance


Thanks,
Manoj

Aussiebear
10-17-2010, 11:14 PM
Hi Manoj, this may be a little more difficult than at first glance given that the titles of the various organisations that you are referring to vary in length and or have no real common key word to extract from.

For example in your own data, look at rows 15 & 20, and the use of the word Society. You would need a new rule just to decide which use of the word Society to keep or delete.

macropod
10-17-2010, 11:17 PM
Hi Manoj,

That would still leave country/region names in the output (eg Malaysian, European, International). I note too that your exclusion list includes one word (Society) that your first post says should be kept ...

vishwakarma
10-18-2010, 12:23 AM
Hello guys,

Sorry for the confusion...

I would like to keep the word society...

Also, is there any way by which I can exclude the region name (as pointed by Macropod)...


Regards,

macropod
10-18-2010, 12:45 AM
Hi Manoj,

is there any way by which I can exclude the region name
Yes - simply add them to your exclusions list. To do this, you will need to include all the forms in which those name appear (eg European, of Europe, etc).

Here's some code to get you started
Sub Abbreviate()
Dim strExcl As String, i As Integer, ocel As Range
Application.ScreenUpdating = False
strExcl = "Academy,Administrators,Agencies,Agency,Agents,Alliance,American,Association ,Chamber,Chambers,"
strExcl = strExcl & "Club,Council,Council,Federation,Forum,Guild,Institute,National,Professional s,Union"
On Error Resume Next
For Each ocel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
If Not IsNumeric(ocel.Value) Then
For i = 1 To UBound(Split(strExcl, ","))
ocel.Value = Trim(Replace(ocel.Value, Split(strExcl, ",")(i), ""))
Next
End If
Next
Application.ScreenUpdating = True
End Sub

vishwakarma
10-19-2010, 11:12 PM
Thanks to everyone for the replies...:hi:


Macropod's code will work for me.. Thanks a lot..