• 518K Members
• 6,415 Online
• 616K Conversations
SOLVED

New Contributor

# Searching For Text

I am looking to create a formula that searches a cell full of text and if it contains words from a certain list, the cell will display "0", if it contains words from a different list, then it will display a "1" all the way up to 4. All of the equations I have tried are IF functions that return only a TRUE/FALSE

The text I want to search is the descriptor attached to the students on the right. On the left is a table of the words that are command terms for each level. I hope this makes sense.

5 Replies
Solution

# Re: Searching For Text

@LWMorton , perhaps

```=IF(SUMPRODUCT(--ISNUMBER(SEARCH(\$AF\$2:\$AF\$8,B2))),
4,
IF(SUMPRODUCT(--ISNUMBER(SEARCH(\$AE\$2:\$AE\$8,B2))),
3,
2,
IF(SUMPRODUCT(--ISNUMBER(SEARCH(\$AC\$2:\$AC\$8,B2))),
1))))```

in C2 and copy it to another cells

# Re: Searching For Text

@Sergei Baklan that has worked perfectly, you are my saviour!

# Re: Searching For Text

@Sergei Baklan for some reason only half the equation works, the terms for level 0 and 1 are coming up as 2s. Any ideas to fix?

# Re: Searching For Text

Sorry, I forgot about 0 column. Plus we have to exclude blank cells, finally as

```=IF(SUMPRODUCT((LEN(\$AF\$2:\$AF\$8)>0)*ISNUMBER(SEARCH(\$AF\$2:\$AF\$8,B2))),
4,
IF(SUMPRODUCT((LEN(\$AE\$2:\$AE\$8)>0)*ISNUMBER(SEARCH(\$AE\$2:\$AE\$8,B2))),
3,
2,
IF(SUMPRODUCT((LEN(\$AC\$2:\$AC\$8)>0)*ISNUMBER(SEARCH(\$AC\$2:\$AC\$8,B2))),
1,
IF(SUMPRODUCT((LEN(\$AB\$2:\$AB\$8)>0)*ISNUMBER(SEARCH(\$AB\$2:\$AB\$8,B2))),
0
)))))```
Related Conversations
Problems with formating text within a cell.
Maciej Fox in Excel on
10 Replies
Search usability
Marcel Ratnam in Microsoft Teams on
2 Replies
reverse text to columns for .csv import
milly87 in Excel on
3 Replies
Search shared mailbox
H. F. in Outlook on
1 Replies