# Find Formula Help

#### Katich

##### Board Regular
Hello,

I have a field that downloads out of my system with the following data. I created a formula, =IFERROR(MID(Q5,FIND("Quantity:",Q5)+10,-FIND("Quantity:",Q5)+2),"") which works fine if there is only one number after the Quantity, but as you can see there can be times that there are more. Once i get that number, i multiply it by a cost. I can't do that with a 2 or more digit result as it will then put in a space. Any suggestions?

 Quantity: 1||Location: 8010-8013-8017||Inventory Status: Issued for Use|| Result = 1

<tbody>
</tbody>

Or

 Quantity: 400|| Result = 4

<tbody>
</tbody>

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Scott Huish

##### MrExcel MVP
If Quantity is always first if it's in the string as in your examples, then:
=IF(LEFT(Q5,8)="Quantity",TRIM(MID(SUBSTITUTE(Q5,"|",REPT(" ",255)),10,254))+0,"")

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:538px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >Q</td><td >R</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Quantity: 1||Location: 8010-8013-8017||Inventory Status: Issued for Use||</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Quantity: 400||</td><td style="text-align:right; ">400</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >R5</td><td >=IF(LEFT<span style=' color:008000; '>(Q5,8)</span>="Quantity",TRIM<span style=' color:008000; '>(MID<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(Q5,"|",REPT<span style=' color:#804000; '>(" ",255)</span>)</span>,10,254)</span>)</span>+0,"")</td></tr><tr><td >R6</td><td >=IF(LEFT<span style=' color:008000; '>(Q6,8)</span>="Quantity",TRIM<span style=' color:008000; '>(MID<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(Q6,"|",REPT<span style=' color:#804000; '>(" ",255)</span>)</span>,10,254)</span>)</span>+0,"")</td></tr></table></td></tr></table> <br /><br />

#### Katich

##### Board Regular
Thank you Scott!! This was perfect!

Replies
3
Views
169
Replies
1
Views
823
Replies
5
Views
846
Replies
4
Views
416
Replies
10
Views
812

1,191,548
Messages
5,987,215
Members
440,085
Latest member
MBecker79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back