Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
Nested if text search | Excel Discussion (Misc queries) | |||
Nested String Filename Variable | Excel Worksheet Functions | |||
Search table for string return next columns value | Excel Worksheet Functions | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions |