Solved

vba - search a cell with text for a particular value

Posted on 2010-11-08
7
253 Views
Last Modified: 2012-05-10
I have a cell filled with text...lengthy...I need to search that text for a particular key word like "a is for apple" and "b is for bat"....if those phases are found in that cell filled with 30 or more sentences then phase_found = "y" else phrase_found = "n'
0
Comment
Question by:GlobaLevel
7 Comments
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
if instr(cl.value,"a is for apple")>0 then phrase_found="y" else phrase_found="n" '

Thomas
0
 
LVL 12

Expert Comment

by:sdwalker
Comment Utility
In vba, use this ...

Sub testString()

  If InStr(1, Range("A1").Value, y, vbTextCompare) > 0 Then
    Range("B1").Value = "y"
  Else
    Range("B1").Value = "n"
  End If
  
End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
You actually don't need VBA (you may still want it though).  Here's a formula approach that uses column B to enter your keyword:

=IF(AND(ISNUMBER(FIND(B2,A2,1)),LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10), ""))>30),"yes","no")

If you don't want column B as a helper, then you can add the phrase to the formula by replacing the word "test" here:

=IF(AND(ISNUMBER(FIND("test",A2,1)),LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10), ""))>30),"yes","no")

Book1.xls
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Expert Comment

by:broomee9
Comment Utility
>>are found in that cell filled with 30 or more sentences

I took this to meant 30 more breaks in the cell (ie. Alt + Enter).  That's what CHAR(10) will find.  If you mean sentences, and you actually want to count periods, then use this formula instead:

=IF(AND(ISNUMBER(FIND("test",A2,1)),LEN(A2)-LEN(SUBSTITUTE(A2, ".", ""))>30),"yes","no")
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Do you just want to check for one phrase?
0
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
Multiple
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
Comment Utility
On the formula side, you can put all the phrases you want to check for in a range and use the following formula, for instance with your phrases in H1:H4 and your text cell in A1


=SUMPRODUCT(ISERROR(FIND(H1:H4,A1))*1)=0

it will return TRUE if all phrases are found, false if they aren't.

in a sub, look at the attached function that matches all phrases defined in the array

Thomas

Function phrase_found(strText As String) As String

Dim arrPhrases(1 To 26) As String, i As Long



arrPhrases(1) = "a is for apple"

arrPhrases(2) = "b is for bat"

'...

arrPhrases(26) = "z is for zebra"

  

For i = 1 To 26

    If InStr(strText, arrPhrases(i)) = 0 Then GoTo NotAllFound:

Next i



phrase_found = "y"

Exit Function



NotAllFound:

phrase_found = "n"

End Function

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now