Nested String Search and Return Value.
 

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.

Nested String Search and Return Value.
 

Try
=IF(ISNUMBER(SEARCH("PR1",B1)),"PR1",IF(ISNUMBER(S EARCH("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)

--
Jacob


"Saurabh Khanna." wrote:


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.

Nested String Search and Return Value.
 

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.

--
Biff
Microsoft Excel MVP


"Saurabh Khanna." wrote in message
...

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.