Removing text from numbers & using result in formula

[RJB78]

Hello - I have raw data that gives me numbers of years (0yrs., 1yrs., 2yrs., ... 17yrs., 18yrs., etc). I want to use just the number of years in another formula that will provide a 'yes/true' value if the record has more than 4 years (among many other conditions). I was able to isolate the number by using =SUBSTITUE(H2,"yrs.",""). But now that isolated number isn't working in my next formula... OR(...,...,BF5>=4). The rest of the formula/ conditions work. I assume this is some text/number issue, but I'm not sure of the solution. If there is a better way to isolate the number from "yrs." I'm open to that as well.

Thank you.

[KOKOSEK]

Please Login or Register  to view this content.

[RJB78]

Thank you KOKOSEK, that seemed to work for a majority of my question. But of course, a new problem has arose in the output of my second formula (not the substitute), the ...OR( ... , BF5>=4) formula. The issue is when there is no data for what the OR( is referencing. When there is a year from the substitute for the unique ID, your solution works. But if there is no data, the OR( formula is still resulting in a YES/TRUE. I currently have a IFERROR( formula to display a "0" if there is no data.

Please let me know if you need more information.

[JohnTopley]

The actual OR statement will help PLUS a sample workbook (see yellow banner at top of page on instructions on attaching a workbook)

[RJB78]

Hi JohnTopley - Unfortunately the file size is to large to upload (38k+KB), and the formula is so interconnected with other tabs I cannot just pull out the few sheets it needs to reduce the size. I will provide the full formulas below.

This is the end formula. Where the undesired result is coming from is in bold. Essentially the final output for this entire formula is Yes or No, if there is an error, the answer is also No.
=IFERROR(IF(AND(($I5*0.15)>((RF!$AE$32*0.04)/$F5),$D5<>"LP",OR($BG5={"A+","A","A-","B+","B","B-","NR"}),$BB5>=0.04,OR($BD5="Yes",BE5>=4,BF5>=4)),"Yes","No"),"No")

For this particular question. We start in a different tab full of raw data and use the substitute formula to remove 'yrs.' from the number ( =SUBSTITUTE(H2,"yrs.","")+0 ). The result of this substitute formula is then Indexed matched into a new tab that contains the large formula above using the following formula:

=IFERROR(INDEX(TAB2!$1:$1048576,MATCH($A5,Tab2!A:A,0),MATCH(BF$3,TAB2!$1:$1,0)),"0")

If there is a matching record, and a number that has resulted from the substitute formula, it is than displayed in column BF5. If there is and error (no result/match), there is a 0 put in column BF. I believe the issue is coming from when there is no matching result and a 0 is displayed. for some reason it is tricking the formula at the top that the result should be YES/TRUE

[FlameRetired]

Hello RJB78. Welcome to the forum.

MS-Off Ver unsure

Please try clicking File >> Account
You should see "Product Information" in the right hand column. Please update your profile. Contributors tailor solutions with those profile details in mind.