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 ="","","")

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 ="","","")

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 ="","","")

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 ="","","")