Formula for pulling multiple strings and returning a value

shanzel

Board Regular
Joined
Nov 13, 2004
Messages
63
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
=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

Board Regular
Joined
Sep 2, 2014
Messages
55
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

Board Regular
Joined
Nov 13, 2004
Messages
63
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

Board Regular
Joined
Nov 13, 2004
Messages
63
I'm not sure this will work since all the cell have a string in it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,632
Messages
5,625,994
Members
416,149
Latest member
Bigpotato 668

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
Top