Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I create a formula out of more than 7 functions in excel?
I am working with excel (office 2003) mainly based on "text-cells". I have
created a formula where I can nestl up to 7 funktions but not more! Goal is to sort out of a certain text, in a cell, a certain word that has to be shown at the end. eg: =IF(ISNUMBER(FIND("CHANGE",J11)),"CHANGE ORDER",(IF(ISNUMBER(FIND("NOTICE",J11)),"NOTICE",( IF(ISNUMBER(FIND("NCR",J11)),"NCR",(IF(ISNUMBER(FI ND("Plus/Minus",J11)),"PLUS/MINUS",(IF(ISNUMBER(FIND("Minutes",J11)),"MOM",(IF (ISNUMBER(FIND("RFI",J11)),"RFI")))))))))))&IF(B11 ="","","") |
#2
|
|||
|
|||
in a separate are in the sheet make 2 columns
in the first column say Col A type your searched values like "CHANGE","NCR","Plus/Minus" In the column next to COL B it type their replacement values " CHANGE ORDER", NCR etc =INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&J1 1&"*","*"&A1:A4&"*")),0))&IF(B11="","","") array entered (ctrl+shift+enter) This way there is no restriction of 7 "Dolores" wrote in message ... I am working with excel (office 2003) mainly based on "text-cells". I have created a formula where I can nestl up to 7 funktions but not more! Goal is to sort out of a certain text, in a cell, a certain word that has to be shown at the end. eg: =IF(ISNUMBER(FIND("CHANGE",J11)),"CHANGE ORDER",(IF(ISNUMBER(FIND("NOTICE",J11)),"NOTICE",( IF(ISNUMBER(FIND("NCR",J11)),"NCR",(IF(ISNUMBER(FI ND("Plus/Minus",J11)),"PLUS/MINUS",(IF(ISNUMBER(FIND("Minutes",J11)),"MOM",(IF (ISNUMBER(FIND("RFI",J11)),"RFI")))))))))))&IF(B11 ="","","") |
#3
|
|||
|
|||
Hi Dolores
Seems to me that =IF(J11="","",IF(J11="CHANGE","CHANGE ORDER",IF(J11="Plus/Minus","PLUS/MINUS",IF(J11="Minutes","MOM",J11)))) will do the trick equally well. However, if you really need to go further, you can get past the 7nested IF issue in a number of ways "Dolores" wrote: I am working with excel (office 2003) mainly based on "text-cells". I have created a formula where I can nestl up to 7 funktions but not more! Goal is to sort out of a certain text, in a cell, a certain word that has to be shown at the end. eg: =IF(ISNUMBER(FIND("CHANGE",J11)),"CHANGE ORDER",(IF(ISNUMBER(FIND("NOTICE",J11)),"NOTICE",( IF(ISNUMBER(FIND("NCR",J11)),"NCR",(IF(ISNUMBER(FI ND("Plus/Minus",J11)),"PLUS/MINUS",(IF(ISNUMBER(FIND("Minutes",J11)),"MOM",(IF (ISNUMBER(FIND("RFI",J11)),"RFI")))))))))))&IF(B11 ="","","") |
#4
|
|||
|
|||
Hello and thanks for advise... but there is still some error as it shows me a
#N/A! if I put: =INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&J1 1&"*","*"&Specs!S2:S14&"*")),0))&IF(B11="","","" ) array entered (ctrl+shift+enter) The column in the "Specs" sheet has all the information that has to be searched for in column "J" and if any of these words are find it should show General instead of the searched word... maybe you have another idea.. I am sitting here in Chile and quite lost as nobody can help me!!! Dolores "N Harkawat" wrote: in a separate are in the sheet make 2 columns in the first column say Col A type your searched values like "CHANGE","NCR","Plus/Minus" In the column next to COL B it type their replacement values " CHANGE ORDER", NCR etc =INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&J1 1&"*","*"&A1:A4&"*")),0))&IF(B11="","","") array entered (ctrl+shift+enter) This way there is no restriction of 7 "Dolores" wrote in message ... I am working with excel (office 2003) mainly based on "text-cells". I have created a formula where I can nestl up to 7 funktions but not more! Goal is to sort out of a certain text, in a cell, a certain word that has to be shown at the end. eg: =IF(ISNUMBER(FIND("CHANGE",J11)),"CHANGE ORDER",(IF(ISNUMBER(FIND("NOTICE",J11)),"NOTICE",( IF(ISNUMBER(FIND("NCR",J11)),"NCR",(IF(ISNUMBER(FI ND("Plus/Minus",J11)),"PLUS/MINUS",(IF(ISNUMBER(FIND("Minutes",J11)),"MOM",(IF (ISNUMBER(FIND("RFI",J11)),"RFI")))))))))))&IF(B11 ="","","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create a if-then formula? | Excel Worksheet Functions | |||
How to create specific formula | Excel Worksheet Functions | |||
Create Formula | Excel Discussion (Misc queries) | |||
How do I create a formula that would allow me to subtract from a d | New Users to Excel | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |