Validating if an alphanumeric value falls between defined min and max

[brianclark75]

I am trying to validate if an alphanumeric value (list of multiple ID numbers format: B125625) falls with a range of four separate Min and max values. Due to the fact that they are alphanumeric, my formulas are not yielding the right results. I've tried formulas such as:

=if(Min1cell<targetcell<max1cell, TRUE,FALSE)

I've tried to use a helper column and take the numeric digits to use the same formula as above (=RIGHT(targetcell),6), but the result is still in text form which doesn't work with the formula.

I've also tried using the Value function =value(right(targetcell,6))

Mins and Maxs

List to be validated as values between the mins and max's

 

[Micron]

The helper column was formatted as numbers? I would expect the formula to work if it was. If not, perhaps you can convert the text value to a number with Value function before passing it to the Min function (i.e. wrap Value within Min function).

 

[etaf]

(=RIGHT(targetcell),6)
that keeps it as text
so
(=RIGHT(targetcell),6)*1
or
(=RIGHT(targetcell),6)+0
etc will convert to a number , then allow you to use the MIn/Max

 

[brianclark75]

The helper column was formatted as numbers? I would expect the formula to work if it was. If not, perhaps you can convert the text value to a number with Value function before passing it to the Min function (i.e. wrap Value within Min function).

cannot simply convert to text, as the values in the list are copied from a raw data paste into another cell. This is why I am looking for a formula that can either convert to numbers or can interpret the range between to alphanumerical limits.

 

[brianclark75]

(=RIGHT(targetcell),6)
that keeps it as text
so
(=RIGHT(targetcell),6)*1
or
(=RIGHT(targetcell),6)+0
etc will convert to a number , then allow you to use the MIn/Max

multiplying by 1 or adding zero did not seem to convert to numbers.

 

[brianclark75]

multiplying by 1 or adding zero did not seem to convert to numbers.

or it did, but I still cannot get my formula to work with it.

 

[brianclark75]

or it did, but I still cannot get my formula to work with it.

Further reviewed my formulas. All is working now, thank you very much!

 

[etaf]

you are welcome, glad you sorted in the end

 

[Micron]

cannot simply convert to text,

Glad you got it figured out because I didn't suggest that.
It's considered customary to post your own solution in case it helps someone else in the future.