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,632
Default Numbers stuck in strings. Does this number belong?


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

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))
myMatch = True
Exit For
End If
ElseIf IsNumeric(vardata(i)) And CInt(vardata(i)) = SearchRng Then
myMatch = True
Exit For
End If
End Function

Claus B.
Office 2016