find the 2nd | and the 3rd | in a string and extract any data in between

[jordanburch]

hey guys I have the following

=MID(LEFT(B2,FIND("|",B2)+2),FIND("|",B2)+1,LEN(B2))

I have the data

| ---Johnny walker | 4264345sw 18th aven, #3, miami, OH 33135 | No | No Im trying to find the address in between the second | and the third | and I cant get the sytax right. Any help is appreciated! Jordan

 

[Fluff]

How about

=TRIM(MID(SUBSTITUTE(B2,"|",REPT(" ",100)),200,100))

 

[jordanburch]

How about

=TRIM(MID(SUBSTITUTE(B2,"|",REPT(" ",100)),200,100))

fluff you sir were born for excel and excel was born for you my friend! thanks again for the millionth time!

Jordan

 

[Fluff]

Glad to help & thanks for the feedback.

 

[jordanburch]

So if I wanted to change it to the 2 nd and 3rd instance would I just change the 200 to 300 and the 100 to 200?

 

[jordanburch]

Glad to help & thanks for the feedback.

So if I wanted to change it to the 2 nd and 3rd instance would I just change the 200 to 300 and the 100 to 200?

 

[Rick Rothstein]

You are asking for two values to be returned, correct? If so, we need to know if any of the values could possibly have a space character in them.

 

[jordanburch]

You are asking for two values to be returned, correct? If so, we need to know if any of the values could possibly have a space character in them.

I'm wanting the text in-between the | value. So basically if I wanted the text in-between the third and fourth and so on and so on. Fluffs formula works for text between the second and third | but I want to be able to modify it for the third and fourth |. Hopefully that makes sense e

 

[Rick Rothstein]

Just change the 200 to 300 (leave the 100's alone).

 

[jordanburch]

Just change the 200 to 300 (leave the 100's alone).

Awesome thanks Rick I appreciate it!