# Formula for pulling multiple strings and returning a value

#### shanzel

##### Board Regular
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### WarPigl3t

##### Well-known Member
=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
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
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
I'm not sure this will work since all the cell have a string in it.

Replies
1
Views
107
Replies
5
Views
45
Replies
8
Views
91
Replies
12
Views
601
Replies
3
Views
123

1,127,028
Messages
5,622,287
Members
415,890
Latest member
macak333

### 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.

### Which adblocker are you using?

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

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