Need help finding a range of text with If statments

[birdo91]

I'm trying to specify a certain number based off of column G where as anything "ge-0/0/0" through "ge-0/0/23" will return a value and "ge-0/0/24" through "ge-0/0/47" will return a different value.

I was able to figure out how to return a value with this formula: =IF(ISNUMBER(SEARCH("ge-0",G2)),12, IF(ISNUMBER(SEARCH("ge-1",G2)),14,IF(ISNUMBER(SEARCH("ge-2",G2)),16,IF(ISNUMBER(SEARCH("ge-3",G2)),18,IF(ISNUMBER(SEARCH("ge-4",G2)),13,IF(ISNUMBER(SEARCH("ge-5",G2)),20, ""))))))

But i would like to split each "ge-*" into two separate values for being able to calculate cable proper cable lengths. I tried =IF(AND(ISNUMBER(SEARCH("ge-0",G2)),G2>="*0",G2<="*23"),6,"") but it did not return the correct value.

Thanks.

 

[Oaktree]

Welcome to the board!

I recommend first having a look at the CHOOSE function, as I believe your:

=IF(ISNUMBER(SEARCH("ge-0",G2)),12, IF(ISNUMBER(SEARCH("ge-1",G2)),14,IF(ISNUMBER(SEARCH("ge-2",G2)),16,IF(ISNUMBER(SEARCH("ge-3",G2)),18,IF(ISNUMBER(SEARCH("ge-4",G2)),13,IF(ISNUMBER(SEARCH("ge-5",G2)),20, ""))))))

could be replaced by the much shorter

=CHOOSE(MID(G2,4,1)+1,12,14,16,18,13,20)

Then, you could replace the "12" with an IF formula for your range criteria.

Perhaps something like this:

=CHOOSE(MID(G2,4,1)+1,IF(AND(SUBSTITUTE(G2,"ge-0/0/","")+0>0,SUBSTITUTE(G2,"ge-0/0/","")+0<=23),6,12),14,16,18,13,20)