# Thread: text string multiple search

1. hi all,

i have a text string and i wan search for three words in it,"approved","replace',"moved" and assign say a number like 1 if any of these words exist.

dudub

2. If the cell is A1, this formula will display a 1 if any are in it and a 0 if none are in it:

=OR(ISNUMBER(SEARCH("approved",A1)),ISNUMBER(SEARC H("replace",A1)),ISNUMBER(SEARCH("moved",A1)))*1

Steve

3. thanks Steve perfect.

4. Let's say that your text string is in cell A1.

Enter the list of search terms in a range of cells; let's say that H1 contains approved, H2 contains replace, and H3 contains moved.

Enter the following array formula in cell B1 (confirm with Ctrl+Shift+Enter):

=SUM(1-ISERROR(SEARCH(\$H\$1:\$H\$3,A1)))

This formula will return the number of matches, i.e. if none of the search terms occurs in the text string, it will return 0, if exactly one of the search terms occurs, it will return 1 etc.

If you prefer to return TRUE if there is at least one match and FALSE if there is none, you can use

=SUM(1-ISERROR(SEARCH(\$H\$1:\$H\$3,A1)))>0

again as an array formula.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•