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 ?

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...

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,
Wouldn't that last instr function still cause it to find ",22" or
",234443" ?

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...

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 ?

nevermind, i see what you did. Thanks for learning us yokels Tom. =)
--
When you lose your mind, you free your life.


"ben" wrote:

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

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...

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 ?

Simpler

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
sStr = "11,22,33"
if instr(1,sStr,",2,") or instr(1,sStr,"2,") = 1 or instr(1,sStr,",2") =
len(sStr)-1 then

--
Regards,
Tom Ogilvy


wrote in message
oups.com...

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 ?

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



--
When you lose your mind, you free your life.


" wrote:


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 ?

That test would fail for

11,33,2

--
Regards,
Tom Ogilvy


"ben" (remove this if mailing direct) wrote in message
...
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



--
When you lose your mind, you free your life.


" wrote:


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 ?

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


--
HTH

Bob Phillips

wrote in message
oups.com...

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 ?

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)

That won't find "2," or ",2"
--
When you lose your mind, you free your life.


" wrote:


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 ?