Solved: Extract Key Words

[vishwakarma]

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 :-

1. Malaysian Association of Convention and Exhibition Organisers and Suppliers :- Extract "Convention and Exhibition Organisers and Suppliers"
2. European Regional Science Association :- Extract "Regional Science"
3. 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]

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]

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]

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]

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]

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]

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,Professionals,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]

Thanks to everyone for the replies...


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