Nested String Search and Return Value.

[Saurabh Khanna.]

Hello,

I want to search for different words in the string and if any of those words
are contained in the searched string then it should return the first found
word.

For example:
B1 = "This is the string to be searched having either PR1, PC1, PB1".

Now I want to search for the words PR1 or PC1 or PB1 in the B1 cell and when
either of these are found then cell A1 should return the first found word,
like in this case it should return PR1 if I search for PR1.

I tried with SEARCH or FIND command but could not successfully complete the
formula to have the nested search using the IF function.

Could any one of you please guide me.


Thank You,
Saurabh Khanna.

 

[Jacob Skaria]

Try
=IF(ISNUMBER(SEARCH("PR1",B1)),"PR1",IF(ISNUMBER(SEARCH("PC1",B1)),"PC1",IF(ISNUMBER(SEARCH("PB1",B1)),"PB1","")))

Or try the below array formula. Apply the formula with Ctrl+Shift+Enter

=MID(B1,MIN(IF(ISERROR(SEARCH({"PR1","PC1","PB1"},B1)),"",
SEARCH({"PR1","PC1","PB1"},B1))),3)

 

[T. Valko]

Try this array formula** :

J1 = PR1
J2 = PC1
J3 = PB1

=INDEX(J1:J3,MATCH(TRUE,ISNUMBER(SEARCH(J1:J3,B1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Christopher Gross]

Thank you so much for this, after two hours of internet searching, and three hours of excel frustration you have solved my problem. . .