find text in a string

[JT]

I'm looking for a suggestion or code on finding text within a string.

For example, I have a field in an Excel spreadsheet that contains a
description of a user issue.

I want to categorize these descriptions by finding a specific word in the
description, e.g., claim, lawsuit, refund, etc. These specific words are
supposed to appear at the beginning of the description but users may place
them anywhere in the description.

I thought of doing using the 'mid' and cycling through the description but
that doesn't seem to be a very efficient way to validate whether a specific
word appears in the description.

Thanks for the help...

 

[John Bundy]

You can do an if statement for each word and see if it is found, the
following checks to see if the word "claim" is in the cell. The reason for
the UCase is that it will not matter if it is capitalized. You don't give a
layout or what you are doing with the result, but this way should be easy to
adapt.
Sub main()
Dim strDesc As String
If InStr(1, UCase(Cells(1, 1)), "CLAIM") > 0 Then strDesc = "Claim"
MsgBox strDesc

End Sub

 

[Rick Rothstein]

Assuming we are talking about VB code (since you posted in a programming
newsgroup and also used the keyword "Mid")... you can use the InStr function
which returns the position within a larger text string where the text you
are searching for is located. Since you have multiple words to search for,
then you will need to loop through your keywords as you loop through your
cells.

 

[Gary''s Student]

Say we are searching for "happiness". Try this:

Sub FindIt()
Dim s As String
s = "happiness"
For Each r In ActiveSheet.UsedRange
v = r.Value
l = Len(v)
v2 = Application.WorksheetFunction.Substitute(v, s, "")
l2 = Len(v2)
addy = r.Address
If l1 <> l2 Then
MsgBox "You can find happiness in cell " & addy
End If
Next

End Sub

 

[Rick Rothstein]

I would put the words to search for in an array and then loop the array.
Something like this...

Sub Main()
Dim X As Long, strDesc As String, Words() As String
' Note -- do not put spaces around commas
Words = Split("Claim,Lawsuit,Refund,Etc", ",")
For X = 0 To UBound(Words)
' Note 1 -- Split function always returns a zero-based array
' Note 2 -- You don't have to UCase the Cell's value,
' just use InStr's optional argument instead.
If InStr(1, Cells(1, 1).Value, Words(X), vbTextCompare) Then
strDesc = Words(X)
Exit For
End If
Next
MsgBox strDesc
End Sub

 

[Rick Rothstein]

InStr function???

--
Rick (MVP - Excel)

Say we are searching for "happiness". Try this:

Sub FindIt()
Dim s As String
s = "happiness"
For Each r In ActiveSheet.UsedRange
v = r.Value
l = Len(v)
v2 = Application.WorksheetFunction.Substitute(v, s, "")
l2 = Len(v2)
addy = r.Address
If l1 <> l2 Then
MsgBox "You can find happiness in cell " & addy
End If
Next

End Sub