Reputation: 9255
For example, the integer range is separated by comma, such as "4450-4499, 4700-4805, 9920-9959". How to check if an integer is in this range?
Upvotes: 0
Views: 2187
Reputation: 18707
Paste values:
A1
= 4450-4499, 4700-4805, 9920-9959
B1
= 4450
or any numberAnd use this formula:
=IFERROR(JOIN("",QUERY(
{ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(A1,", ")),"(\d+)")*1),
ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(A1,", ")),"-(\d+)")*1)},
"where Col1 <= "&B1&" and Col2 >= "&B1&"",0))*1,0)>0
Will return true bacause 4450
is in range 4450-4499
.
Upvotes: 1
Reputation: 10776
This function takes a number and the range and performs the check, but beware that it has no precautions, so the data provided must fit the format you gave:
function inRange(number, range) {
range = range.split(", ");
range = range.map(function(x) {return x.split("-")});
for (var i in range) {
if (number >= range[i][0] && number <= range[i][1]) {
return true;
}
}
return false;
}
Upvotes: 0