# How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

1. ## How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

Hi all,

Can a FIND or SEARCH formula be limited to detect multiple 'OR' values only if the values are the first or last in a string? (I don't need a single formula that checks for both conditions; I can use 2 formulas if it's significantly easier).

For example, can =OR(ISNUMBER(SEARCH({"dog","mouse","elephant"},A1))) be modified to detect "Dog 123" or "Mouse123 ABC" or "123 XYZ Elephant" while ignoring cells containing any of the values in an intermediate position?

Thanks for any help!

2. ## Re: How to limit FIND or SEARCH to either the first or last word in a string?

=if(or(left(a1,3)="abc",right(a1,3)="abc"),"replacewithtruevalue","replacewithfalsevalue")

3. ## Re: How to limit FIND or SEARCH to either the first or last word in a string?

BTW - I understand you were looking for find or search, but it's not necessary. Simple is usually better.

4. ## Re: How to limit FIND or SEARCH to either the first or last word in a string?

Maybe (If all you want is True/False):
Formula:
`Please Login or Register  to view this content.`

Replace WORD with the cell address of the lookup val

5. ## Re: How to limit FIND or SEARCH to either the first or last word in a string?

Just to show some alternatives:
=OR(LEFT(A1;LEN("ABC"))="ABC";RIGHT(A1;LEN("ABC"))="ABC")
=AND(SEARCH("ABC";A1);OR(ISERROR(SEARCH("?"&"ABC";A1));ISERROR(SEARCH("ABC"&"?";A1))))
//Ola

6. ## Re: How to limit FIND or SEARCH to either the first or last word in a string?

Sorry folks, I really screwed up my initial problem statement by neglecting some 'minor' details, namely that the search involves multiple 'OR' values where each one can consist of a different number of characters.

I've revised the question, hopefully clear this time.

7. ## Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

I think you'd be better off using helper columns and smaller formulas.

8. ## Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

If you have a long list of possible search strings then it would be easier with those in a table.

Without a table

=OR(COUNTIF(A1,{"dog*","mouse*","elephant*","*dog","*mouse","*elephant"}))

With a table, i.e. listing dog, mouse, elephant in E1:E3

=OR(COUNTIF(A1,CHOOSE({1,2},"*"&\$E\$1:\$E\$3,\$E\$1:\$E\$3&"*")))

The second formula must be array confirmed with Shift Ctrl Enter.

9. ## Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

Originally Posted by jason.b75
If you have a long list of possible search strings then it would be easier with those in a table.

Without a table

=OR(COUNTIF(A1,{"dog*","mouse*","elephant*","*dog","*mouse","*elephant"}))

With a table, i.e. listing dog, mouse, elephant in E1:E3

=OR(COUNTIF(A1,CHOOSE({1,2},"*"&\$E\$1:\$E\$3,\$E\$1:\$E\$3&"*")))

The second formula must be array confirmed with Shift Ctrl Enter.
I can't count how many times I've overlooked the simplicity/power of wildcards. Both versions appear to work for all cases, xlnt, and yes I do have many search values so the table version is great, THANKS VERY MUCH!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1