ISNUMBER(SEARCH) alternative

[Excelself]

Hi There!

I am using Excel 2016 version and wanted to add another ISNUMBER(SEARCH) function but my Excel version will not accept more than 6 nested ISNUMBER(SEARCH) functions. Is there another way of doing that?

Thank you

Here is what I have.

I just need to ad another search for R20
=IF(ISNUMBER(SEARCH("R15",H2)),0.15,IF(ISNUMBER(SEARCH("R5",H2)),0.05,IF(ISNUMBER(SEARCH("ESL",H2)),1,IF(ISNUMBER(SEARCH("RDG",H2)),1,IF(ISNUMBER(SEARCH("READING * ",H2)),1,IF(ISNUMBER(SEARCH("STDY",H2)),1,"0"))))))

 

[Rick Rothstein]

I just need to ad another search for R20
=IF(ISNUMBER(SEARCH("R15",H2)),0.15,IF(ISNUMBER(SEARCH("R5",H2)),0.05,IF(ISNUMBER(SEARCH("ESL",H2)),1,IF(ISNUMBER(SEARCH("RDG",H2)),1,IF(ISNUMBER(SEARCH("READING * ",H2)),1,IF(ISNUMBER(SEARCH("STDY",H2)),1,"0"))))))

I see you are using the SEARCH function... is there other text in cell H2 along with the text you are searching for? If so, can you give us a sample of what the entire text in cell H2 might look like?

 

[Fluff]

Along with what Rick has said, what do you want to return if R20 is in the cell?

 

[Excelself]

Here is what I have "

Any student with an R15 code will get .15hr of instructional time in reading while attending CNA classes. I have written 6 searches and I need one more where the search function will search for R20 for any student attending HS LA classes and return .33 hr. Unfortunately, I am getting a message stating I cannot use more that 6 nested searches. I need 7 searches to complete my project.

StudId AttendDt AttendPeriodNo AbsReasonCd AttendCd ExcusedAbs EnrDt CourseName
1619419 7/22/2019 3 3197 375 Y 5/20/2019 CNA *M15 *R15
1619419 7/22/2019 5 0 375 N 5/20/2019 MATH 4 *M
1619419 8/19/2019 7 2918 375 Y 5/20/2019 HS LA 2 *R20
1619419 7/31/2019 6 3197 375 Y 5/20/2019 TABE Stdy 1 *R
1619419 8/2/2019 1 3197 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/2/2019 2 3197 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/2/2019 3 3197 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/2/2019 5 3197 375 Y 5/20/2019 MATH 4 *M
1619419 8/7/2019 1 0 375 N 5/20/2019 CNA *M15 *R15
1619419 8/16/2019 3 3197 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/19/2019 1 2918 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/19/2019 2 2918 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/19/2019 3 2918 375 Y 5/20/2019 CNA *M15 *R15
1619419 8/19/2019 5 2918 375 Y 5/20/2019 MATH 4 *M
1619419 8/19/2019 6 2918 375 Y 5/20/2019 TABE Stdy 1 *R

 

[jasonb75]

I was assuming additional text in the cells from the wildcard on the 5th search. See if this helps

=LOOKUP(1E+100,SEARCH({"*","R20","R15","R5","ESL","RDG","READING * ","STDY"},H2),{0,0.33,0.15,0.05,1,1,1})

Given the nesting limit, I think it fair to assume that you're using excel 2003, hopefully the formula above will work with that.

 

[Excelself]

I will try it and let you know. Thank you for your time.