Calculable Text?

[Qkta6667]

Hi Guys,

Is there a possibility to turn the data in book 1 into a calculation?

Example: 

Bourbon Vanilla : Dairy Ice Cream

2 x 5 ltr [2]

The 2 x 5ltr should be 2*5ltr 

or

Bloomer : Malted Brown

7x900g [7]

The 7x900g should be 7*900g

The Data coming from a third party as it is. 

Any method to make this Text to calculable.

Thanks for your time 

Frank 

[#2]

@Qkta6667 

For Quantity, this formula in I2:

=IFERROR(PRODUCT(0+MID(SUBSTITUTE(LEFT(H2,MATCH(1,INDEX(-MID(LEFT(H2,FIND("[",H2&"[")-1),ROW($1:$99),1),)))&"x1x1x1x1x1","x",REPT(" ",25)),25*{0,1,2,3,4,5}+1,25)),"N/A")

For Units, this formula in J2:

=IF(I2="N/A","N/A",TRIM(RIGHT(LEFT(H2,FIND("[",H2&"[")-1),LEN(LEFT(H2,FIND("[",H2&"[")-1))-MATCH(1,INDEX(-MID(LEFT(H2,FIND("[",H2&"[")-1),ROW($1:$99),1),)))))

The above will work for any units of measurement.

Regards

[#3]

Hi @Jos_Woolley 

Thank you very much. It is brilliant.

With your and @Sergei Baklan help I can finish my "homework". 

And I have to admit I learned a lot about Excel with your formula.

Thanks

Kind Regards

Frank

[#4]

@Qkta6667 

Hi Frank,

Glad to hear it!

Cheers,

Jos