Extracting Text between 2nd and 3rddelimiter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

exocet.am39@gmail.com - 18 Jan 2008 16:56 GMT

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

Reply to this Message

Mike H - 18 Jan 2008 17:35 GMT

Maybe this

=MID(MID(MID(SUBSTITUTE(A1,"_","^",A2),1,256),FIND("^",SUBSTITUTE(A1,"_","^",A2)),256),2,FIND("_",MID(MID(SUBSTITUTE(A1,"_","^",A2),1,256),FIND("^",SUBSTITUTE(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

> hi,
>
[quoted text clipped - 9 lines]
>
> db

Reply to this Message

Mike H - 18 Jan 2008 17:46 GMT

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

> hi,
>
[quoted text clipped - 9 lines]
>
> db

Reply to this Message

exocet.am39@gmail.com - 18 Jan 2008 18:08 GMT

thanks! i did a crude solution using find, mid and len. but i like
your formulae better!

db

Reply to this Message