find only numbers in text string

[billyheanue]

Looking for a formula to start with below, and return the following. Basically multiplying 6*50, 5*100, 10*100, 12*50, and so on.

Start with:

CS/6 BX/50 EA

CS/5 BX/100 EA

CS/10 BX/100 EA

CS/12 BX/50 EA Returns: 300 500 1000 600

<colgroup><col></colgroup><tbody>
</tbody>

 

[AkaTrouble]

this works for examples shown

=(MID(A1,FIND("/",A1,1)+1,2))*50

EDIT

this is slightly more robust formula

=(MID(A1,FIND("/",A1,1)+1,LEN(LEFT(A1,FIND(" ",A1,1)))-4))*50

 

[AkaTrouble]

sorry i musunderstood the question

 

[63falcondude]

Try

=TRIM(MID(A1,FIND("CS",A1)+3,2))*TRIM(MID(A1,SEARCH("BX",A1)+3,3))

 

[AkaTrouble]

Try

=TRIM(MID(A1,FIND("CS",A1)+3,2))*TRIM(MID(A1,SEARCH("BX",A1)+3,3))

much better than solution i was working on

=(MID(A1,FIND("/",A1,1)+1,LEN(LEFT(A1,FIND(" ",A1,1)))-4))*(LEFT(TRIM(LEFT(RIGHT(SUBSTITUTE("/"&A1,"/",REPT(" ",300)),300),300)),FIND(" ",TRIM(LEFT(RIGHT(SUBSTITUTE("/"&A1,"/",REPT(" ",300)),300),300)),1)))

 

[Trouttrap2]

Another alternative would be to do TextToColumn. Delimit your data by a 'space' and '/', then export only the column with numbers.

Excel 2010
	A	B	C	D	E
1	Data		Text to column		Result
2	CS/6 BX/50 EA		6	50	300
3	CS/5 BX/100 EA		5	100	500
4	CS/10 BX/100 EA		10	100	1000
5	CS/12 BX/50 EA		12	50	600
Sheet1

Cell Formulas
Range	Formula
E2	=C2*D2
E3	=C3*D3
E4	=C4*D4
E5	=C5*D5