Parsing text using ISNUMBER and FIND

I need to sort through thousands of strings to classify them as citations for books, journal articles, or internet based sources.

The final formula in column F in the worksheet attached is supposed to classify the string as "Book" if "Ed." was found in the String in Column A, "Journal Article" if one or more of the numbers (0-9) were found in the substring in Column D, "Internet" if "http" (but no number) was found next to the colon. If no numbers were found next to the colon, and no http was found either, it was to be called "Other."

As you can in the attachment, the classification seems to be going well until it encounters Row #15. This is actually a journal article, but is classified as "Other." This is perhaps because, apart from the numbers next to the colon, there also are some alpha characters ("ds"), but I don't understand why this should matter.

Try this in F2 and fill down. It changes a lot of the results, but row 15 is now Journal Article.

Untested

Formula:

Please Login or Register  to view this content.

Step through the formula with the Evaluate Formula tool. You will see that this part -- IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},D9)),"Journal Article","Other") -- returns an array of "Journal Article" and "Other", depending on whether or not the numbers were found. The first result of this array will make it into the final result.

It is by pure chance that some Journal Articles have a zero in the text in column D, and the first entry in the FIND result array is "Journal Article".

You want to check for ANY number, so you need to convert the result of the FIND into a series of 0 for not found and 1 for found, then sum that.

Try

=IF(ISNUMBER(SEARCH("ed.",A2)),"Book",IF(E2="No",IF(SUM(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},D2))*1),"Journal Article","Other"),"Internet"))