ExcelBanter - Find a theme in a article in a single cell

On Sep 4, 2:00*pm, Prashanth KR
wrote:
Hi,

I want to automate a process in which Iam looking out for a theme in an
article which is placed in a single cell A1. (The article may have more than
200 words).

For eg. the actual message in an article may have "Patients with Aortic
Stenosis benefit from lipid-lowering therapy with Vytorin".

The problem is, the words may not be in the same order or having the same
spellings or synonyms. But the message in the article will say that.

Kindly, give me some inputs or any other idea so that I can capture it
without really reading the whole article.

Here's hoping for a positive reply.

Warm regards,
Prashanth KR.

Hello Prashanth,

The one problem I see with the task you have put forward is the limits
on data stored within a single cell in Excel. Excel has a cap of 255
characters per cell, but will allow entry of more than that. Beyond
that limit, you may experience data loss and I am not sure if it will
search beyond that range.

You could build an array of words that you want to search for in the
article and have Excel highlight the cells that contain values that
match.

The following code will prompt the user for input on what word/text
they wish to find, then which column contains the articles, and
finally will filter the range to only those articles containing that
text.

Good luck!

Steven

Sub ArticleFind()
Dim ArFndVal As Variant
Dim MyCell As Range

If ActiveSheet.AutoFilter = True Then
ActiveSheet.AutoFilter = False
End If

' Prompts the user for the text to find
ArFndVal = Application.InputBox( _
Prompt:="Enter text to find:", _
Title:="Find", _
Default:="", _
Type:=2)
If ArFndVal = False Then
MsgBox ("You must provide text to search in the
articles")
Exit Sub 'User cancelled
End If
'Prompts the user to identify the column containing the articles to
search
Set MyCell = Application.InputBox("Select header", Type:=8)

MyCell = Range(MyCell.Columns).Address
Range("a1").AutoFilter Field:=MyCell.Column, Criteria1:="*" & ArFndVal
& "*"
End Sub