Find Location of multiple instances of characters

[Ronan1452]

Hi

I have been trying to find a solution to this problem for ages.

We have numeric info buried among text. I am interested in identifying the number of people going for a package and the package value. (in red below)

5.00 x Food(p) @ €12.50 = €62.50, 5.00 x IB(p) @ €2.50 = €12.50, 2.00 x IB(p) @ €4.99 = €9.98, 2.00 x Food(p) @ €24.00 = €48.00

I am trying to design formula to identify the location of each occurrence of say "Food".

Once i have the various locations I can use Right, Left, MID to pull pricing info associated with the "Food"

I have no problem identifying the location of the first "food", and counting the number of the occurrences. I cant seem to get my head around finding the location of the second and subsequent "Food"s.

Any help much appreciated!

 

[AlphaFrog]

<br /><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 /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Item</td><td style="font-weight: bold;text-align: center;;">Count</td><td style="font-weight: bold;text-align: center;;">Pos 1</td><td style="font-weight: bold;text-align: center;;">Pos 2</td><td style="font-weight: bold;text-align: center;;">Pos 3</td><td style="font-weight: bold;text-align: center;;">Pos 4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">5.00 x Food(p) @ €12.50 = €62.50, 5.00 x IB(p) @ €2.50 = €12.50, 2.00 x IB(p) @ €4.99 = €9.98, 2.00 x Food(p) @ €24.00 = €48.00</td><td style="text-align: center;;">Food</td><td style="text-align: center;;">2</td><td style="text-align: center;;">8</td><td style="text-align: center;;">103</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><br /><br /><table 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">C2</th><td style="text-align:left">=(<font color="Blue">LEN(<font color="Red">A2</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A2,B2,""</font>)</font>)</font>)/LEN(<font color="Blue">B2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">C2>=1,FIND(<font color="Red">B2,A2</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(<font color="Blue">C2>=2,FIND(<font color="Red">B2,A2,D2+1</font>), ""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">C2>=3,FIND(<font color="Red">B2,A2,E2+1</font>), ""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">C2>=4,FIND(<font color="Red">B2,A2,F2+1</font>), ""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Ronan1452]

That is brilliant - works perfectly.

Many thanks!

 

[Ronan1452]

Thanks again for the help above - however I am not out of the woods yet!

I need to pull various numeric info from the text strings. I have been using find and mid for this.

The problem i now have if that when using the mid function I need to specify how many characters to return. However due the the varying length of numbers this can range from 1.00 - 999.00 or 4 - 7 characters.

In the example before I need to find the quantity of "Food" for each instance of "food" in the string.

I have each "Food" position and was trying to use mid to return the quantities.

Any ideas?

 

[AlphaFrog]

<b>Excel 2003</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 /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Item</td><td style="font-weight: bold;text-align: center;;">Count</td><td style="font-weight: bold;text-align: center;;">Pos 1</td><td style="font-weight: bold;text-align: center;;">Qty1</td><td style="font-weight: bold;text-align: center;;">Price1</td><td style="font-weight: bold;text-align: center;;">Pos 2</td><td style="font-weight: bold;text-align: center;;">Qty2</td><td style="font-weight: bold;text-align: center;;">Price2</td><td style="font-weight: bold;text-align: center;;">Pos 3</td><td style="font-weight: bold;text-align: center;;">Qty3</td><td style="font-weight: bold;text-align: center;;">Price3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"><font color="Red">5.00</font> x Food(p) @ €<font color="Green">1.50</font> = €7.50, 5.00 x IB(p) @ €2.50 = €12.50, 2.00 x IB(p) @ €4.99 = €9.98, <font color="Red">999.00</font> x Food(p) @ €<font color="Green">24.00</font> = €23,976.00</td><td style="text-align: center;;">Food</td><td style="text-align: center;;">2</td><td style="text-align: center;;">8</td><td style="text-align: center;;"><font color="Red">5.00<font></td><td style="text-align: center;;"><font color="Green">1.50</font></td><td style="text-align: center;;">103</td><td style="text-align: center;;"><font color="Red">999.00</font></td><td style="text-align: center;;"><font color="Green">24.00</font></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></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 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">D2</th><td style="text-align:left">=IF(<font color="Blue">C2<1,"",FIND(<font color="Red">B2,A2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(<font color="Blue">C2<1,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">TRIM(<font color="Teal">LEFT(<font color="#FF00FF">A2,D2-4</font>)</font>)," ",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">C2<1,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A2,FIND(<font color="#FF00FF">"=",A2,D2</font>)-2</font>),"@ €",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">C2<2,"",FIND(<font color="Red">B2,A2,D2+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IF(<font color="Blue">C2<2,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">TRIM(<font color="Teal">LEFT(<font color="#FF00FF">A2,G2-4</font>)</font>)," ",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(<font color="Blue">C2<2,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A2,FIND(<font color="#FF00FF">"=",A2,G2</font>)-2</font>),"@ €",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=IF(<font color="Blue">C2<3,"",FIND(<font color="Red">B2,A2,G2+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=IF(<font color="Blue">C2<3,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">TRIM(<font color="Teal">LEFT(<font color="#FF00FF">A2,G2-4</font>)</font>)," ",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IF(<font color="Blue">C2<3,"",TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A2,FIND(<font color="#FF00FF">"=",A2,K2</font>)-2</font>),"@ €",REPT(<font color="Teal">" ",99</font>)</font>),99</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Ronan1452]

Again brilliant. Many thanks!

Could you explain the logic to me?

 

[AlphaFrog]

TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,G2-4))," ",REPT(" ",99)),99)))

Uses the Position number(G2) and returns the left most text up to the position number minus 4. So the result text ends in the value we are searching for
e.g 5.00 x Food(p) @ €1.50 = €7.50, 5.00 x IB(p) @ €2.50 = €12.50, 2.00 x IB(p) @ €4.99 = €9.98, 999.00


TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,G2-4))," ",REPT(" ",99)),99)))

Substitutes any single space with 99 spaces. So each word in the previous result text is separated by 99 spaces.


TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,G2-4))," ",REPT(" ",99)),99)))

Takes the last (right most) 99 characters and TRIMS it (removes leading and trailing spaces). The last 99 characters is the last word (999.00) preceded by a bunch of spaces which are trimmed away.