| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested MID and FIND - OR?
Hello all!
Does anyone know how I may be able to search a string and extract text from it using the MID and FIND function with multiple FINDS? I have a spreadsheet for which I have a lengthy bit of text for which I have set up to currently extract ",IE". I need to adapt the formula =MID(Dump!A1,FIND(",IE.",Dump!A1)-2,5) to also search for either ",MA" or a ",MC". These characters will always be unique and there will not be a combination of either in the text string. The string will have either one of the three but not two or all of them. I thought there may be an OR function I could use but am having no luck. Thank you for your time any any help you may be able to provide. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested MID and FIND - OR?
First of all, I prefer to use SEARCH rather than FIND because FIND is
case-sensitive, although that might be important to you. Here's one way of doing it: =IF(ISNUMBER(SEARCH(",IE.",Dump!A1)),MID(Dump!A1,S EARCH(",IE.",Dump! A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MA",Dump!A1)),MID(Dump!A1,SEA RCH (",MA",Dump!A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MC",Dump!A1)),MID(Dump! A1,SEARCH(",MC",Dump!A1)-2,5),"") All one formula - hopefully you can see how it could be extended to include other conditions. Hope this helps. Pete On Jan 13, 3:15*pm, Brenda wrote: Hello all! Does anyone know how I may be able to search a string and extract text from it using the MID and FIND function with multiple FINDS? *I have a spreadsheet for which I have a lengthy bit of text for which I have set up to currently extract ",IE". *I need to adapt the formula =MID(Dump!A1,FIND(",IE.",Dump!A1)-2,5) to also search for either ",MA" or a ",MC". *These characters will always be unique and there will not be a combination of either in the text string. *The string will have either one of the three but not two or all of them. *I thought there may be an OR function I could use but am having no luck. Thank you for your time any any help you may be able to provide. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested MID and FIND - OR?
Thanks Pete! I appreciate your help. This did the trick and I learned something! "Pete_UK" wrote: First of all, I prefer to use SEARCH rather than FIND because FIND is case-sensitive, although that might be important to you. Here's one way of doing it: =IF(ISNUMBER(SEARCH(",IE.",Dump!A1)),MID(Dump!A1,S EARCH(",IE.",Dump! A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MA",Dump!A1)),MID(Dump!A1,SEA RCH (",MA",Dump!A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MC",Dump!A1)),MID(Dump! A1,SEARCH(",MC",Dump!A1)-2,5),"") All one formula - hopefully you can see how it could be extended to include other conditions. Hope this helps. Pete On Jan 13, 3:15 pm, Brenda wrote: Hello all! Does anyone know how I may be able to search a string and extract text from it using the MID and FIND function with multiple FINDS? I have a spreadsheet for which I have a lengthy bit of text for which I have set up to currently extract ",IE". I need to adapt the formula =MID(Dump!A1,FIND(",IE.",Dump!A1)-2,5) to also search for either ",MA" or a ",MC". These characters will always be unique and there will not be a combination of either in the text string. The string will have either one of the three but not two or all of them. I thought there may be an OR function I could use but am having no luck. Thank you for your time any any help you may be able to provide. . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested MID and FIND - OR?
Glad to hear it, Brenda - thanks for feeding back.
Pete On Jan 13, 6:15*pm, Brenda wrote: Thanks Pete! *I appreciate your help. *This did the trick and I learned something! "Pete_UK" wrote: First of all, I prefer to use SEARCH rather than FIND because FIND is case-sensitive, although that might be important to you. Here's one way of doing it: =IF(ISNUMBER(SEARCH(",IE.",Dump!A1)),MID(Dump!A1,S EARCH(",IE.",Dump! A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MA",Dump!A1)),MID(Dump!A1,SEA RCH (",MA",Dump!A1)-2,5),"")& IF(ISNUMBER(SEARCH(",MC",Dump!A1)),MID(Dump! A1,SEARCH(",MC",Dump!A1)-2,5),"") All one formula - hopefully you can see how it could be extended to include other conditions. Hope this helps. Pete On Jan 13, 3:15 pm, Brenda wrote: Hello all! Does anyone know how I may be able to search a string and extract text from it using the MID and FIND function with multiple FINDS? *I have a spreadsheet for which I have a lengthy bit of text for which I have set up to currently extract ",IE". *I need to adapt the formula =MID(Dump!A1,FIND(",IE.",Dump!A1)-2,5) to also search for either ",MA" or a ",MC". *These characters will always be unique and there will not be a combination of either in the text string. *The string will have either one of the three but not two or all of them. *I thought there may be an OR function I could use but am having no luck. Thank you for your time any any help you may be able to provide. .- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
| Nested IF, reports incorrectly, can't find anything like it to ref | Excel Worksheet Functions | |||
| Calc cost pro rata (nested IF's and macro to find empty row) | Excel Worksheet Functions | |||
| nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
| Nested If statement to find greater than but less than numbers | Excel Worksheet Functions | |||
Linear Mode
