InStr function question

[richmarin]

I am using Excel 2002

I have this problem with Instr. Imagine a cell that contains the
following text

11,22,33

If I use the following command, instr(1,activecell.Value,"2",0)

The function returns a 4.

But, what if I wanted to function to look for exactly a 2? Because 2 is
part of 22. The function found it.

Is there a better function that will help me find exactly what I am
looking for ?

 

[Tom Ogilvy]

sStr = "11,22,33"
if instr(1,sStr,",2,") or instr(1,sStr,"2,") = 1 or instr(1,sStr,",2") =
len(sStr)-1 then

 

[Guest]

Unless somebody has a better way


(11,22,33) is active cell

i = instr(1,activecell,"2")
if mid(activecell,i+1,1) = "," then
'it is a two
else
it is not exactly two
end if

 

[Tom Ogilvy]

That test would fail for

11,33,2

 

[Guest]

Tom,
Wouldn't that last instr function still cause it to find ",22" or
",234443" ?

 

[Bob Phillips]

This seems to work

Function Singleton(val, test)
Dim ipos As Long
Dim iStart As Long
Dim fOK As Boolean

iStart = 1
Do
ipos = InStr(iStart, val, test, 0)
fOK = ipos <> 0 And (ipos = Len(val) Or Mid(val, ipos + 1, 1) <>
test)
iStart = ipos + 2
Loop Until fOK Or ipos = 0

If ipos = 0 Then
Singleton = False
Else
Singleton = ipos
End If
End Function

Sub testSingleton()
Debug.Print Singleton("1,2,3", "2")
Debug.Print Singleton("11,22,33", "2")
Debug.Print Singleton("11,2,33", "2")
End Sub

 

[Guest]

nevermind, i see what you did. Thanks for learning us yokels Tom. =)

 

[Roman]

The function returns 4 because "2" is on 4th position in your text. It
was looking exactly for "2".

If you want ",2," to be found use instr(1 , activecell.value ,
",2," , 0)

 

[Tom Ogilvy]

Simpler

sStr = "," & ActiveCell.Value & ","
if instr(1,sStr,",2,") then
msgbox "contains 2"
End if

 

[Guest]

That won't find "2," or ",2"