chech if a cell contains a substring

[Vik]

Hi All,

I am new in Excel programming.
I need to check if a cell contains a substring, something like "*excel*".

First, I tried

Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

Only it looks like .Find cannot be used more than once. I have a nested loop
and used .Find in a wrapper loop.

I need some alternative to .Find.

Thanks!

 

[Mike Fogleman]

Check out this link for a formula:
http://www.exceltip.com/st/Finding_a_substring_within_a_string/874.html

If you want to do this from VBA using a formula, see VBHelp on "Evaluate"

Mike F

 

[Guest]

The following code found all instances of the substring "excel" within the
range A1:C10. The Selection and MsgBox code is for demo purposes only.

I think what you are looking for is the FindNext method. Note that, here,
"d" serves as the "After" argument to FindNext (i.e. FindNext(d)). In other
words, find the next cell containing the substring, skipping the first cell.
The code then sets d to this new cell and so forth. Also note that the
FindNext method will form a nonterminating loop, finding the first cell again
once it comes to the end of the range and repeating from there. That's why we
record the address of the first instance (adr) and stop it at this point.

Sub FindSubString()
Dim d As Range
Dim rng As Range
Dim cdnName As String
Dim adr As String

cdnName = "excel"
Set rng = Range("A1:C10")
Set d = rng.Find(cdnName)
If Not d Is Nothing Then
d.Select
MsgBox d.address
adr = d.Address ' record address of first instance
Do
Set d = rng.FindNext(d)
d.Select
MsgBox d.Address
Loop Until d.Address = adr ' stop when loops around to first instance
End If
End Sub

 

[Vik]

Greg,

Let me be more specific.

Let's say I have a datasheet as follows.
____A______B__
1 aaaa
2 aaaa exc1
3 aaaa 3exc
4 bbbb exc2
5 bbbb
6 bbbb

Below is my macro -

Sub test()
Sheets("TEST").Activate
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range
Dim d As Range
Set rng1 = ActiveSheet.Range("A1:A6")
Set c = rng1.Find("aaaa", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)
If Not c Is Nothing Then
firstAddress = c.Address
cntAaaE = 0
cntAaaNoE = 0
Do
myRow = c.Row
Set rng2 = ActiveSheet.Cells(myRow, 2)
Set d = rng2.Find("exc", LookIn:=xlValues, LookAt:=xlPart,
MatchCase:=False)
If Not d Is Nothing Then
cntAaaE = cntAaaE + 1
Else
cntAaaNoE = cntAaaNoE + 1
End If
Set c = rng1.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
MsgBox "cntAaaE=" & cntAaaE & ", cntAaaNoE=" & cntAaaNoE
End Sub

I expected it to print "cntAaaE=2, cntAaaNoE=1".
I need to count lines that match "aaaa" in a 1st column AND having "exc" as
a part of a value in a 2nd column.
But unfortunately, rng1.FindNext(c) does not do what I want.

Appreciate your help.

 

[Vik]

Mike,

On the web I have found 2 solutions -
Solution 1:
Using the COUNTIF function in the following IF statement:
=IF(COUNTIF(A2,""*""&B2&""*"")>0,""Found"",""Not Found"")

Solution 2:
Using the ISNUMBER and FIND functions, as follows:
=IF(ISNUMBER(FIND(B2,A2)),""Found"",""Not Found"")

I tried to follow, but I cannot figure out the syntax -

tmp = Evaluate(CountIf(A2, "*exc*") > 0, "Found", "Not Found")

Should I put the formula into some cell and evaluate it?

Thanks

 

[Guest]

First, be advised that my comments on the Find method are based only on a
modest amout of experience.

My understanding is that the FindNext method continues the search started by
the previous use of the Find method. However, your code reuses the Find
method and resets the What argument (string to be looked for) to "exe" from
"aaaa". It then goes on to use FindNext(c) with the incorrect assumption that
it will continue looking for "aaaa".

I rewrote your code as shown below with some adaption to personal style. I
instead resorted to the Like operator thereby eliminating the need to reuse
Find. Note that I also establish the identity of the neighboring cell using
Set cc = c(1, 2). This is the same as using Set cc = c.Offset(0, 1), just a
little more concise. This is more efficient than getting the row number and
then using the Cells method: "Cells(myRow, 2)". Also note that I refrain from
activating sheet "TEST" which is unnecessary.

Sub test()
Dim rng As Range
Dim c As Range, cc As Range
Dim adr As String
Dim x As Long, xx As Long
Dim ws As Worksheet

Set ws = Sheets("TEST")
x = 0: xx = 0

Set rng = ws.Range("A1:A12")
Set c = rng.Find("aaaa", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
adr = c.Address
Do
Set cc = c(1, 2) 'or c.Offset(0, 1)
If cc.Value Like "*ece*" Then
x = x + 1
Else
xx = xx + 1
End If
Set c = rng.FindNext(c)
Loop While c.Address <> adr
End If
MsgBox "x=" & x & ", xx=" & xx

End Sub

 

[Guest]

Correct the minor error in my code where the Like operator looks for "*ece*"
instead of "*exe*".

Regards,
Greg

 

[Vik]

Greg,

It works great and it is exactly what I need.
On more thing. The method Like is case sensitive. If I need to ignore case,
do I have to convert to lower case contents of a cell before?

Thanks!

 

[Guest]

Put the declaration "Option Compare Text" at the top of the module. This
should make it case insensitive.

Regards,
Greg