If/Then function with substring search

Hello, I have a single variable composed of thousands of course names and I need to transform it into a single 5-level variable in excel (math, reading, science, social studies, other).

In variable 1, each course type is represented by potentially hundreds of variations (e.g. Math 1, Math 2, Advanced Math, Mathematics) - too many to enter into an conditional IF/THEN function by hand. The "other" category is for subjects like art, typing, marketing, etc. that are not one of the 4 core subjects listed above.

I would like to know if there is a formula that will give me something like the following: IF column A contains the substring "math" return "math", OR IF column A contains the substring "reading" return "reading, OR IF column A contains the substring "science" return science, OR IF the column A contains the substring "social studies" return "social studies" IF column A does not contain any of these substrings then return "other".

The substrings may be at the beginning, end, or middle of a given string. I have found different functions that I think could be combined to make this work but am not sure how... Thank you!

Hello
Maybe the following might work:

Please Login or Register  to view this content.

Here the range to search is A2:A20. The search criteria is hard coded but it could be a cell reference.

Hope this helps.
DBY

Wow, thank you! That worked great for the "math" course. Now, the same column contains 3 other "target" courses (reading, science, social studies) that need to be returned when found. I think I need to include "OR" functions so that excel can search the column for any of the four subject substrings and return the respective substrings, but I'm struggling with what that would look like and where to put the "other" statement.

maybe something like this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Math",$A$2:$A$20))))>0,"Math",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Reading",$A$2:$A$20))))>0,"Reading",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Science",$A$2:$A$20))))>0,"Science",IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Social Studies",$A$2:$A$20))))>0,"Social Studies","Other"))))

may have to check close bracket count, i just copied, edit, pasted next categories, so i may have missed 1...or 2...

hmm...on re-reading your last post, it looks like column a may contain any or all of these categories?
if so, need a sample worksheet & a sample of what you expect the result to look like to give you a better solution

Screen Shot 2012-10-30 at 11.08.07 PM.pngExample1.xlsx

Hi, thanks so much. Here is a screen shot and a sample file. Hope they come through ok.

I also realized that I may need to search for multiple substrings in column A for each subject in column B. For example, for the subject "math" I need to identify not only cells that contain the word "math" but also those that include the words "geometry" and "algebra." Likewise, for the subject "reading" I need to identify cells that contain the word "reading" but also "English" and "Literature"

ok, i see what you want, the real problem is matching a particular 'course' to a certain 'subject' -- ie. anything math related returns "Math" or anything science related returns "Science",etc.., yes?

See the attached

sorry if i read the first post wrong, thought we were lookinin to to match 4 categories and a catchall category of "Other"...maybe i've completely mis-read the recquirements here

Thank you, both! I tried Teethless mama's code on the real data and it works perfectly! Thank you both! dredwolf, you read that correctly - it just got a little more complicated when there was more than one substring (column A) per subject (column B). Looks like there were multiple ways to skin this cat. Many, many thanks!

Jessica