Search for a String Within Text

Ever needed to find out which cells contain a specific string of text? This function can search strings in a range, looking for specified text. It returns a result identifying which cells contain the text, as shown in Figure 4.16.

ContainsText(Rng,Text) The arguments are

Rng—The range in which to search. Text—The text for which to search.

Function example:

Function ContainsText(Rng As Range, Text As String) As String Dim T As String Dim myCell As Range

For Each myCell In Rng 'look in each cell

If InStr(myCell.Text, Text) > 0 Then 'look in the string for the text

If Len(T) = 0 Then 'if the text is found, add the address to my result T = myCell.Address(False, False)

Else

T = T & "," & myCell.Address(False, False) End If End If Next myCell ContainsText = T End Function

Figure 4.16

Return a result identifying which cell(s) con-tain(s) a specified string.

0 0

Post a comment