InStr question

[CG Rosen]

Good day group,

Trying to use the InStr function in VBA to find if various numbers is
contained in a string.

If InStr(c.value, "String") > 0
Then..........................................etc

The c.value can be from 1 to 50. The string can look like "1,15,21" . If the
c.value =1 that number
is then found 3 times. How is it possible to do a distinction that 1 is only
found one time and 2 one time and say 21 one time and so on? Do I need some
other kind of separator in the string?

Hope my question is understandable.

Brgds

cgr

 

[JLGWhiz]

If you search for strings, it returns the position of the first occurrence
of the string. Numbers are individual charcters and are treated
differently. 1 is 1 even though it may be part of 15 or 120 or 351 or 218.
InStr would find the first occurrence of 1. Maybe you wnat to use InStrB
which searches by byte.

 

[Dave Peterson]

How about:

Option Explicit
Sub testme()

Dim myStr As String
Dim myStrToTest As String
Dim cVal As String 'just to show how
Dim cValToUse As String
Dim myPos As Long
Dim HowMany As Long

myStr = "1,21,31"
cVal = "21"

'remove any possible spaces
myStrToTest = Replace(myStr, " ", "")
'make sure each element has a leading and trailing comma
myStrToTest = "," & myStr & ","

'same thing with cVal
cValToUse = "," & cVal & ","

'now look for that modified value in the modified string
myPos = InStr(1, myStrToTest, cValToUse, vbTextCompare)

If myPos = 0 Then
MsgBox "not found"
Else
MsgBox "woohoo! Found it"
End If

'if the strings were:
mystrtotest = ",1,21,31,21,"
cValToUse = ",21,"
'(after modifying them)
'and you wanted to count how many times 21 appeared:

HowMany = (Len(myStrToTest) - Len(Replace(myStrToTest, cValToUse, ""))) _
/ Len(cValToUse)
MsgBox HowMany

End Sub

 

[Joel]

It looks like you have CSV data. Use the spl;it function to put the various
numbers into an array

NumStr = "1,15,21"
MyArray = split(NumStr,",")

for each Num in MyArray
'put your code here
next Num

 

[Rick Rothstein]

The InStr function cannot return the numbers you seem to be indicating it
does (InStr returns a position number, not a count), which probably means
you have code that you have not shown us. It is kind of hard to give you
advice if we don't know what you are attempting to do. Post the code you
have (working or not) so we can get an idea of what you are trying to do. If
you can supplement that with a description of what you want your code to be
doing, that would be good as well.

 

[Mike H]

Hi,

Yiu could do this. the message boxes are to illustrate it is working and
wouldn't be necessary in your final code

Sub SplitOut1()
searchstring = "1"
Dim numstring As Variant
numstring = "99,15,21,1"
b = Split(numstring, ",")
For x = 0 To UBound(b)
If b(x) = searchstring Then
found = True
Exit For
End If
Next
If Not found Then
MsgBox searchstring & " Not Found"
Exit Sub
End If
'do things
MsgBox searchstring & " Found"

End Sub

Mike

 

[Don Guillett]

Does this help?

Sub searchnumberinstring()
Dim mn As Integer
c = ActiveCell
mn = 5
mnl = Len(Application.Trim(mn))
'MsgBox mnl
For i = 1 To Len(c)
'MsgBox Mid(c, i, mnl)
If Mid(c, i, mnl) = mn Then
Exit For
End If
Next i
MsgBox mn & " found at position " & i
End Sub

 

[CG Rosen]

Thanks for helpful input from you all. Dave's answer is the one that covers
my needs the best.

Thank you

cgr

 

[Rick Rothstein]

Here is a shorter, slightly different approach to do what you want...

Sub Test()
Dim myStr As String
Dim cVal As Long
Dim HowMany As Long
myStr = "1,21,31,1,42"
cVal = "1"
HowMany = UBound(Split(Replace("," & myStr & _
",", " ", ""), "," & cVal & ","))
MsgBox "There are " & HowMany & " occurrences of """ & _
cVal & """ in """ & myStr & """"
End Sub

You can test HowMany in your code if you need to differentiate between none
and some occurrences. Using Dave's If..Then approach, just do this after you
assign the value to the HowMany variable as in my above code...

If HowMany = 0 Then
MsgBox "Not found"
Else
MsgBox "Woohoo! Found it"
End If

--
Rick (MVP - Excel)

Thanks for helpful input from you all. Dave's answer is the one that
covers my needs the best.

Thank you

cgr