Calculable Text?

Copper Contributor

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 

or

Bloomer : Malted Brown7x900g [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 

 

 

 

 

 

 

 

22 Replies

@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

Hi Jos,

thanks for this, but now how can i turn this into a total volume in an another cell what can see the difference between Liter and Kilogram?

Thanks
Frank

@Qkta6667 

 

Ah, so then that first solution was not in fact an option for you. Are you now saying that in fact my second proposal is what you require, i.e. to return 10 ltr and 6300g?

 

If so, can you clarify the precise nature of the cell contents? For example, do they always have a number in square brackets at the end, e.g. [2], [7]? Is this value to be taken into consideration, or ignored?

 

Also, what version of Excel are you using?

 

Regards

Hi Josh

Sorry,
This some example without brackets
Chicken Legs 1 x11 kg
Chicken Fillet : 150-180g 1x60each
Rhubarb : Whole Fruit 1 x 1 kg

When there is a value in the bracket that referral to the pack not important nut if it helps you can use.
If I'm able to get organized the majority of the cells I'm ok
Thanks
Frank




@Qkta6667 

 

Thanks. I think there's probably too much variation here for a single formula. For example, what's the expected result for Chicken Fillet : 150-180g 1x60each?

 

Also, you need to clarify whether each of the entries you give is in a single cell or not. For example, for

 

Chicken Legs 1 x11 kg

 

is this in two separate cells, or one? 

 

I think it would help matters considerably if you posted an actual workbook containing a few examples together with expected results.

 

And you didn't say what version of Excel you're using. Office 365?

 

Regards

Hi Jos.

 

I have an Excel 2010 version  

I attached the full data base for you.

My target here to calculate a menu price based on this database.

For the chicken fillet I just have to know the price of 1pc

For milk I want to get /liter price

If you can show me the path to follow that would be great.

 

Thanks

Frank 

FYI I've replied with a solution but the admin at this site are withholding that post as part of their auditing. They say it will be released within 24 hours.

 

Regards

Hi Josh,

 

A big Thank you for your help and your time.

 

have Good day

Frank

@Qkta6667 

 

In any case, there was an inaccuracy in that solution, so I see no harm in posting a corrected version:

 

In row 2:

 

=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},H2)),PRODUCT(0+MID(SUBSTITUTE(LEFT(H2,MIN(FIND({"ltr","kg","g","ml","each","portions"},H2&"ltrkggmleachportions")-1))&"x1x1x1x1x1","x",REPT(" ",25)),25*{0,1,2,3,4,5}+1,25))&LOOKUP(1,0/FIND({"ltr","g","kg","ml","each","portions"},H2)," "&{"ltr","g","kg","ml","each","portions"}),"N/A")

 

and copied down.

 

Regards

Hi Jos

It is looking awesome and it is working! You are a star. Thanks
Frank

@Qkta6667 

 

Glad to hear it! Just be aware that the formula only accounts for the specific units of measurement that I found within your file, i.e. "ltr","kg","g","ml","each","portions", and would need expanding to include any other units of measurement that may crop up.

 

Regards

That is just fine, Even if i'm just a chef i might be able to manage :)
If not i'll run back to you if that is ok?

Thanks
Frank

@Qkta6667 

 

Then I'm 'just an Excel guy'! :)

 

Of course, reply to this thread whenever and I'll pick it up.

 

Cheers

@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.

@Sergei Baklan , wasn't aware that functions available only to vba could be used outside it. I'm wondering, in general, what could be the reasons to prefer writing a named formula over writing a UDF? A handy ability when the job is almost manageable without macro, or any other important benefits? If you have time to list a few or may be refer to an external artical, not urgent. Thanks!

@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.

Hi Sergei,     @Sergei Baklan 

 

This method is great too and working perfectly.

Just 1 tiny thing: Can I change the formula somehow and the results coming up in two different cells?

Like: 5.600g  ---->  5.600 |  g  

And thank you for your time

 

Kind Regards

Frank