Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if text search
I have entered the following formula in my spreadsheet. There are about 100
different text strings in column A that I can categorize based on certain sub-strings. Creating these categories in the data will make the pivot tables easier to create later. Here's my formula: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5","Non e of the Above"))))) This formula works like a charm. However, there is one other string, call it "String6", I need to include. The following formula does not work: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5", IF(NOT(ISERR(SEARCH("String6",A4))),"String6","Non e of the Above")))))) Excel seems to have a problem with ISERR in the final nested IF. I can split the formula up into multiple columns, but that's not as elegant as I'd like it to be. Am I doing something wrong? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if text search
=IF(ISNUMBER(SEARCH("String",A4)),MID(A4,SEARCH("S tring",A4),7),"None of the
Above") "jims2994" wrote: I have entered the following formula in my spreadsheet. There are about 100 different text strings in column A that I can categorize based on certain sub-strings. Creating these categories in the data will make the pivot tables easier to create later. Here's my formula: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5","Non e of the Above"))))) This formula works like a charm. However, there is one other string, call it "String6", I need to include. The following formula does not work: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5", IF(NOT(ISERR(SEARCH("String6",A4))),"String6","Non e of the Above")))))) Excel seems to have a problem with ISERR in the final nested IF. I can split the formula up into multiple columns, but that's not as elegant as I'd like it to be. Am I doing something wrong? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if text search
That worked, and it's cleaner, too. Still not sure why my formula didn't
work, but I'm not going to lose sleep over it. Thanks. "Teethless mama" wrote: =IF(ISNUMBER(SEARCH("String",A4)),MID(A4,SEARCH("S tring",A4),7),"None of the Above") "jims2994" wrote: I have entered the following formula in my spreadsheet. There are about 100 different text strings in column A that I can categorize based on certain sub-strings. Creating these categories in the data will make the pivot tables easier to create later. Here's my formula: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5","Non e of the Above"))))) This formula works like a charm. However, there is one other string, call it "String6", I need to include. The following formula does not work: =IF(NOT(ISERR(SEARCH("String1",A4))),"String1", IF(NOT(ISERR(SEARCH("String2",A4))),"String2", IF(NOT(ISERR(SEARCH("String3",A4))),"String3", IF(NOT(ISERR(SEARCH("String4",A4))),"String4", IF(NOT(ISERR(SEARCH("String5",A4))),"String5", IF(NOT(ISERR(SEARCH("String6",A4))),"String6","Non e of the Above")))))) Excel seems to have a problem with ISERR in the final nested IF. I can split the formula up into multiple columns, but that's not as elegant as I'd like it to be. Am I doing something wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fun with text functions - search for text | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Limit to nested IF(ISNUMBER(SEARCH)) functions? | Excel Worksheet Functions | |||
Can COUNTIF be nested to search two ranges | Excel Worksheet Functions | |||
Returning text from nested IF and Vlookup statements | Excel Worksheet Functions |