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?

=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?

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?