Numbers stuck in strings. Does this number belong?
 

Hello,

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


Kind Regards,
Jay

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

Numbers stuck in strings. Does this number belong?
 

On Tuesday, May 8, 2018 at 2:58:14 PM UTC-4, jlclyde wrote:
Hello,

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


Kind Regards,
Jay

Cross-post: https://groups.google.com/forum/#!to...sc/DTw02f3uFW4