Hi Guys, Is there a possibility to turn the data in book 1 into a calculation?Example: Bourbon Vanilla : Dairy Ice Cream2 x 5 ltr [2]The 2 x 5ltr should be 2*5ltr orBloomer : Malted Brown7x900g [7]The 7x900g should be 7*900gThe Data coming from a third party as it is. Any method to make this Text to calculable. Thanks for your time Frank

Qkta6667 Bit expanded variant with using of built-in EVALUATE() function - staying on Q2 add named formula =TEXT( IFERROR( EVALUATE( SUBSTITUTE(LEFT( IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2), MAX(ISNUMBER(VALUE(MID(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2),ROW(INDIRECT("1:"&LEN(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2)))),1)))* ROW(INDIRECT("1:"&LEN(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2)))) ) ), "x","*" ) ), 0),"#,##") & " " & TRIM( MID( IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2), MAX( ISNUMBER(VALUE(MID(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2),ROW(INDIRECT("1:"&LEN(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2)))),1)))* ROW(INDIRECT("1:"&LEN(IFERROR(LEFT(H2,SEARCH("[",H2)-1),H2)))))+1, 10) ) let call it PackCalc. In Q2=PackCalc and drag it down. Which words are as units doesn't matter. The only workbook shall be saved as macro enabled.

amit_bhola EVALUATE is not a VBA-only function. It is packaged into Excel from 4.0 time and could be used as any other built-in function, e.g. SUM(), but only through named function mechanism. To parse the string and convert it to the form which could be evaluated - I don't thing formulas are more complex compare to VBA coding. The only the latest shall be bit more compact and readable, as far as we don't use LET(). So, the only reason was to avoid VBA programming.

Qkta6667 So what are your expected results here? Do you simply want to replace "x" with "*"? Or do you want to see the total weight/volume returned to another cell, i.e. (for your two examples): 10 ltr and 6300g? Regards

Hi Jos,Both option is acceptable.Thanks Frank

Qkta6667 Well, the first is very simple: if cell A1 contains 2 x 5 ltr [2] then this formula in B1 =SUBSTITUTE(A1,"x","*") will return 2 * 5 ltr [2] Regards

Forum Discussion

Qkta6667

Copper Contributor

May 25, 2020

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 [2]

The 2 x 5ltr should be 2*5ltr

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

developer

excel

Formulas and Functions

Macros and VBA

22 Replies

Jos_Woolley
Iron Contributor
May 25, 2020
Qkta6667

So what are your expected results here? Do you simply want to replace "x" with "*"? Or do you want to see the total weight/volume returned to another cell, i.e. (for your two examples): 10 ltr and 6300g?

Regards
- Qkta6667
  Copper Contributor
  May 25, 2020
  Hi Jos,
  Both option is acceptable.
  Thanks Frank
  - Jos_Woolley
    Iron Contributor
    May 25, 2020
    Qkta6667
    
    Well, the first is very simple: if cell A1 contains
    
    2 x 5 ltr [2]
    
    then this formula in B1
    
    =SUBSTITUTE(A1,"x","*")
    
    will return
    
    2 * 5 ltr [2]
    
    Regards