 Highlighted

# Calculable Text?

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 

The 2 x 5ltr should be 2*5ltr

or

 Bloomer : Malted Brown 7x900g 

The 7x900g should be 7*900g

The Data coming from a third party as it is.

Any method to make this Text to calculable.

Frank

22 Replies
Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

Thank you very much. It is brilliant.

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

Thanks

Kind Regards

Frank

Highlighted

Hi Frank,