ISNUMBER(SEARCH) alternative

Excelself

New Member
Joined
Mar 16, 2018
Messages
22
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"))))))
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
 
Upvote 0
Along with what Rick has said, what do you want to return if R20 is in the cell?
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top