• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple search in a cell [SOLVED]

R

rameshkumarsharma

New Member
Hi Team,


I am facing an issue in the Search formula. I would like to seach a particular Alphanumeric in a cell any it can be (W1,W2,W3,W4 till w10). But only one alphanumeric(W1,W2 etc). I have used the below formula but it's not working. Please help me out in order to get the correct results.


=IF(SEARCH("W1",AA3),"W1",IF(SEARCH("W2",AA3),"W2",IF(SEARCH("W3",AA3),"W3")))


Regards,

Ramesh
 
Hi Narayan,


Thanks for the formula. But i don't want to print the entire line but i want to show only w1 or w2 whatever the value in the cell.


Regards,

Ramesh
 
Hi Ramesh ,


This was not clearly specified originally.


Try this :


=IF(NOT(OR(ISNUMBER(SEARCH({"W1","W2","W3"}&" ",H13&" ")))),"Not Found",INDEX({"W1","W2","W3"},MMULT(--ISNUMBER(SEARCH({"W1","W2","W3"}&" ",H13&" ")),{1;2;3})))


This is an array formula , to be entered using CTRL SHIFT ENTER.


Narayan
 
Hi,


You may also try this regular formula.

Code: 
=LOOKUP(999,SEARCH({"W1","W2","W3","W4","W5","W6","W7","W8","W9","W10"},A1,1),{"W1","W2","W3","W4","W5","W6","W7","W8","W9","W10"})
 
Nice formulas.


Shrivallabha: you could simplify that a bit:

=LOOKUP(999,SEARCH("W"&{1,2,3,4,5,6,7,8,9,10},A1,1),"W"&{1,2,3,4,5,6,7,8,9,10})


...or even this:

=LOOKUP(999,SEARCH("W"&ROW(1:10),A1,1),"W"&ROW(1:10))


...or if you needed that last one to be resistant to row insertions you could use this:

=LOOKUP(999,SEARCH("W"&ROW(INDEX(A:A,1):INDEX(A:A,10)),A1,1),"W"&ROW(INDEX(A:A,1):INDEX(A:A,10)))


...although these simplifications are only really useful if say you needed to search for W1 to W1000 and you didn't want to type out a very long lookup list.
 
Hi, rameshkumarsharma!

May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable (Click on your name, Edit). Thank you.

Regards!
 
Hi Jeffrey,


At the outset it looks simplified and sleeker formula but in fact, it is adding few more calcs ;). Not that we need to bother about them in this case.


Yes but it becomes neat and necessary trick if we have to use it for a large W combinations.
 
You must log in or register to reply here.
Back
Top