Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Text between 2nd and 3rddelimiter
hi,
i have the following text: AAA_llac_MAR_99_eg i am trying to write a formula to extract the text between the second and third occurance of '_" (the delimiter). Basically i want to extract MAR. I am trying to do this using the MID and FIND statement. Any help would be appreciated! db |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Text between 2nd and 3rddelimiter
Maybe this
=MID(MID(MID(SUBSTITUTE(A1,"_","^",A2),1,256),FIND ("^",SUBSTITUTE(A1,"_","^",A2)),256),2,FIND("_",MI D(MID(SUBSTITUTE(A1,"_","^",A2),1,256),FIND("^",SU BSTITUTE(A1,"_","^",A2)),256))-2) A1 is your string and A2 tells it where to start extraction from, in this case the send "_" so A2 is 2 Mike " wrote: hi, i have the following text: AAA_llac_MAR_99_eg i am trying to write a formula to extract the text between the second and third occurance of '_" (the delimiter). Basically i want to extract MAR. I am trying to do this using the MID and FIND statement. Any help would be appreciated! db |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Text between 2nd and 3rddelimiter
Maybe a bit more flexible, try this:-
=MID(MID(MID(SUBSTITUTE(A1,A3,"^",(A2-1)),1,256),FIND("^",SUBSTITUTE(A1,A3,"^",(A2-1))),256),2,FIND(A3,MID(MID(SUBSTITUTE(A1,A3,"^",( A2-1)),1,256),FIND("^",SUBSTITUTE(A1,A3,"^",(A2-1))),256))-2) A1= Your string A2= number of the word to extract (As written this formula won't extract the first word) A3 = Delimeter (In your case _) Mike " wrote: hi, i have the following text: AAA_llac_MAR_99_eg i am trying to write a formula to extract the text between the second and third occurance of '_" (the delimiter). Basically i want to extract MAR. I am trying to do this using the MID and FIND statement. Any help would be appreciated! db |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Text between 2nd and 3rddelimiter
thanks! i did a crude solution using find, mid and len. but i like
your formulae better! db |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting text | Excel Discussion (Misc queries) | |||
Extracting Text from the right | Excel Worksheet Functions | |||
EXTRACTING TEXT | Excel Discussion (Misc queries) | |||
extracting certain text | Excel Programming | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |