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!
Last edited by Buzzed Aldrin; 07-14-2012 at 01:45 PM. Reason: Wrong initial problem statement
=if(or(left(a1,3)="abc",right(a1,3)="abc"),"replacewithtruevalue","replacewithfalsevalue")
If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
BTW - I understand you were looking for find or search, but it's not necessary. Simple is usually better.
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
Last edited by protonLeah; 07-13-2012 at 08:24 PM.
Ben Van Johnson
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
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.
I think you'd be better off using helper columns and smaller formulas.
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!Originally Posted by jason.b75
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks