Finding Text Between Two Instances

[Chip_Excel]

Hi Everyone,
I am trying to figure out how to return the text between two different instances of the same symbol.

For example, I have this in a cell:
100% Cotton / 55.0 in / 3.2 oz/sq yd / 120 x 70 / 40 NE x 40 NE / Yarn Dye

I want to be able to to extract all the information between the fifth and sixth "/", which is "40 NE x 40 NE".
The number of characters in between the slashes is dynamic, one cell might contain "40 NE x 40 NE" and another one just "40x40".
I also want to be able to count the instances left to right or right to left.
Let me know if anyone has any ideas.
Chip

 

[schielrn]

If it is always between the 5th adn 6th, this is one way:

Excel Workbook
	A	B
1	100% Cotton / 55.0 in / 3.2 oz/sq yd / 120 x 70 / 40 NE x 40 NE / Yarn Dye	40 NE x 40 NE
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",5))+2,LEN(A1)),SEARCH("/",MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",5))+2,LEN(A1)))-2)

Hope that helps.

 

[schielrn]

I guess it could be a little shorter like this:

=MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",5))+2,FIND("~",SUBSTITUTE(A1,"/","~",6))-FIND("~",SUBSTITUTE(A1,"/","~",5))-3)

Also, Welcome to the Board!

And this assumes your data will never have a tilda ~ in it. If it can, then you will want to use a character that will not ever be used.

 

[Chip_Excel]

Hi Schielrn,
Thank you so much for the above. It worked like a charm. And no worries there will never be a tilda in the information. Thanks again!
Chip

 

[Haseeb Avarakkan]

Also;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">100% Cotton / 55.0 in / 3.2 oz/sq yd / 120 x 70 / 40 NE x 40 NE / Yarn Dye</td><td style=";">40 NE x 40 NE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A1,"/",REPT(<font color="Purple">" ",LEN(<font color="Teal">A1</font>)</font>)</font>),LEN(<font color="Green">A1</font>)*5,LEN(<font color="Green">A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />