GlobaLevel
asked on
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'
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
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,A 2,1)),LEN( A2)-LEN(SU BSTITUTE(A 2, 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("tes t",A2,1)), LEN(A2)-LE N(SUBSTITU TE(A2, CHAR(10), ""))>30),"yes","no")
Book1.xls
=IF(AND(ISNUMBER(FIND(B2,A
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("tes
Book1.xls
>>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("tes t",A2,1)), LEN(A2)-LE N(SUBSTITU TE(A2, ".", ""))>30),"yes","no")
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("tes
Do you just want to check for one phrase?
ASKER
Multiple
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thomas