Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

vba - search a cell with text for a particular value

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
GlobaLevel
Asked:
GlobaLevel
1 Solution
 
nutschCommented:
if instr(cl.value,"a is for apple")>0 then phrase_found="y" else phrase_found="n" '

Thomas
0
 
sdwalkerCommented:
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
 
TracyVBA DeveloperCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TracyVBA DeveloperCommented:
>>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
 
NorieVBA ExpertCommented:
Do you just want to check for one phrase?
0
 
GlobaLevelProgrammerAuthor Commented:
Multiple
0
 
nutschCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now