# Calculable Text?

Highlighted
Occasional Contributor

# 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

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.

Frank

22 Replies
Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

Hi Jos,
Both option is acceptable.
Thanks Frank
Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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
Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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

Highlighted

Hi Josh,

have Good day

Frank

Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

Hi Jos

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

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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
Highlighted

# Re: Calculable Text?

Then I'm 'just an Excel guy'!

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

Cheers

Highlighted

# Re: Calculable Text?

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.

Highlighted

# Re: Calculable Text?

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

Highlighted

# Re: Calculable Text?

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.

Highlighted

# Re: Calculable Text?

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