May 25 2020 04:29 AM
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
May 25 2020 04:40 AM
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
May 25 2020 04:47 AM
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
May 25 2020 04:57 AM
May 25 2020 05:02 AM
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
May 25 2020 05:11 AM
May 25 2020 05:24 AM
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
May 25 2020 05:35 AM
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
May 25 2020 06:25 AM
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
May 25 2020 06:31 AM
Hi Josh,
A big Thank you for your help and your time.
have Good day
Frank
May 25 2020 06:31 AM
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
May 25 2020 06:39 AM
May 25 2020 06:42 AM
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
May 25 2020 06:54 AM
May 25 2020 07:08 AM
Then I'm 'just an Excel guy'! :)
Of course, reply to this thread whenever and I'll pick it up.
Cheers
May 25 2020 08:08 AM
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.
May 25 2020 12:29 PM
@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!
May 25 2020 01:28 PM
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.
May 27 2020 02:44 AM
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