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 |
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 |
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 |
Extracting Text between 2nd and 3rddelimiter
thanks! i did a crude solution using find, mid and len. but i like
your formulae better! db |
All times are GMT +1. The time now is 04:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com