Excel - find text within a string and return that text

Question

For example, I have a long list of sentences (I like ice cream, I like apples, I like dogs, etc) and I need to search each of those sentences to find if each sentence has "cheese, bananas, or cats" and only those three things - that is all I care about. If the sentences dont say those three words, I need the result or return to be other BUT if the sentence does contain any one of those three words I need the result to return THAT word. - If the sentence is "I like cheese" the formula should return "cheese" and so on. I have tried many different formulas that either do not work OR it says i have too many conditions. Please help

Answer 1

See how close this comes to what you're looking for.

_{Click on image for larger view}

I may be assuming potential complexity that you don't have, so this might not be the simplest solution.

Column A contains the sentences. I assumed your examples just happened to have the target word at the end in every case, but that the word could appear anywhere in the sentence. If it will only ever appear at the end, there are simpler ways to look for it. I also assumed that there could be differences in capitalization, like if the word appears at the beginning of the sentence.

Column D contains a list of your target words. Putting them in a list simplifies things because most solutions will need to use the word for some kind of lookup, then use it again for the result. The list also simplifies changing the targets without needing to alter all of your formulas. If you really don't want the list, and even hiding it isn't enough, you can hard code the values in the formula instead of using cell references.

Column B contains the result. I assumed you only care about the target word and not its capitalization (you want the same result regardless of where it appears in the sentence or its capitalization). This formula leaves the cell blank if there is no match (actually containing a null). This is more efficient for finding the matching records than sticking some kind of result in every cell. If you want something else to show for those cases, the formula will be more complicated.

The formula in B1 (copy it down the column as needed, or pre-populate a bigger range than you need), is:

=IF(ISNUMBER(SEARCH($D$1,A1)),$D$1,"")&IF(ISNUMBER(SEARCH($D$2,A1)),$D$2,"")&IF(ISNUMBER(SEARCH($D$3,A1)),$D$3,"")

This just searches (non-case-sensitive) for each target word. If it finds the word, it adds it to the result string. Otherwise, it adds a null. The ampersands concatenate the results for each target. So the result string will contain either null or the matching word.

Since the question states that you only have a few target words, this isn't too unwieldy. If you could actually have a much longer list of target words, it could make sense to use a different approach.

Answer 2

If you write text from A1 on column A

=RIGHT(SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")));LEN(A1)-FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))

is your solution

That's how it look

Answer 3

=IF(OR(RIGHT(SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")));LEN(A1)-FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))) = "Cats";RIGHT(SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")));LEN(A1)-FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))) = "Bananas";RIGHT(SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")));LEN(A1)-FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))) = "Cheese");RIGHT(SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")));LEN(A1)-FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))); "")

This look crazy but it works!I just modified the first answer above.