Formula for pulling multiple strings and returning a value

[shanzel]

I have an excel file that I'm trying to pull information into one column with a numeric code instead of a string using the following criteria:

If column B, C, D has the string "3-Pt, EXCEEDS, ARMY OR NAVY" return the number "1"
If column E has the string "DEV-FD" return the number "2"
If column F has the string "DEV-NF" return the number "3"
If column G has the string "18-HRS" return the number "4"
If column H, I, J, K has the string "EMBA, ONLINE, SE, 8B" return the number "5"
If column L has the string "12-HRS" return the number "6"
If none of the above applies the return a "0"


Does anyone know of a way to pull this?


CODE 1-3PEAT 1-EXCEEDS 1-MIL 2-DEV FND 3-DEV-NF 4-DEV => 18 hrs 5-EMBA 5-ONLINE-NF 5-SE STUDENT 5-8B Session 6-UG >12 HRS GRAD
N N N N N N N N SE N N
3-Pt N N N N N N N N N N
N N N N DEV-NF N N N N N N
N EXCEEDS N N N N N N N N N
N N N DEV-FND N N N N N N N
N N N N N 18-HRS N N N N N
N N ARMY N N N N N N N N
N N N N N N EMBA N N N N
N N N N N N N ONLINE N N N
N N NAVY N N N N N N N N
N N N N N N N N N N N
N N N N N N N N N N 12-HRS

 

[WarPigl3t]

=IF(NOT(ISNUMBER(E1)), 2, 0)
This is saying if the value of E1 is not a number, then return the number 2. Otherwise it will return 0 if it is a number. Because if it is not a number, it is a string.
Now I'll leave it up to you to create your nested IF statements.

 

[mcmahobt]

Is each piece of text that you're trying to convert in its own cell? If it is, you could use an IF statement to convert based on text.

 

[shanzel]

No, Army, Navy is in one cell. and the rest are in a cell by there selves. However, they are to many to use IF, since you can only use up to 7 IF statements. I tried to use the following but I'm missing the mark on this one.

=IF(ISERROR(SEARCH({"3-Pt","EXCEEDS","ARMY","NAVY"},$B2:$D2)),1,0),+IF($E2="DEV-FD",2,+IF($F2="DEV-NF",3,+IF($G2="18-hrs",4,+IF(ISERROR(SEARCH({"EMBA","ONLINE","SE","8B"},$H2:$K2)),5,0),+IF($L2="12-HRS",6))))

 

[shanzel]

I'm not sure this will work since all the cell have a string in it.