View Single Post
  #2   Report Post  
Old May 8th 18, 08:49 PM posted to microsoft.public.excel.misc
Claus Busch Claus Busch is offline
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,625
Default Numbers stuck in strings. Does this number belong?

Hi,

Am Tue, 8 May 2018 11:58:11 -0700 (PDT) schrieb jlclyde:

I have a string that resembles 1,2,5-7,13. I have another cell that has a number in it. I want to know if that number is in the first number.

So if I have:
1 - I want to return true
2 - I want to return true
6 - I want to return true (6 is not one of the numbers in the string but it is between 5 and 7.
10 - I want to return false


your string in A1, the number you are searching for in B1.
Then call the function in the sheet with
=myMatch(A1,B1)

Function myMatch(myRng As Range, SearchRng As Range) As Boolean
Dim vardata As Variant, varTmp As Variant
Dim i As Integer

vardata = Split(myRng, ",")
For i = LBound(vardata) To UBound(vardata)
If Not IsNumeric(vardata(i)) Then
varTmp = Split(vardata(i), "-")
If SearchRng = CInt(varTmp(0)) And SearchRng <= CInt(varTmp(1))
Then
myMatch = True
Exit For
End If
ElseIf IsNumeric(vardata(i)) And CInt(vardata(i)) = SearchRng Then
myMatch = True
Exit For
End If
Next
End Function


Regards
Claus B.
--
Windows10
Office 2016