Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
NorieData ProcessorCommented:
Do you just want to check for one phrase?
0
 
GlobaLevelAuthor 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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