excel-lambda-CONVERTRECIPE – Convert recipe ingredients to different units in Excel

How it works

Here’s how it works:

CONVERTRECIPE has two parameters:

1. ingredient – this is a sentence describing the volume of an ingredient. For this version of this function, this must start with a number (the measurement) followed by an optional unit of measure, and there must be a non-breaking space separating the measurement and the description of the ingredient. 
2. convert_to – this is a unit of measure to convert the ingredient text to. Supported are all measurements supported by the CONVERT function. Additionally, I’ve included pseudonyms “fl oz” for “fluid ounce” (convert uses “oz”), “oz” for “dry ounce” (convert uses “ozm”) and “tbsp” for “tablespoon” (convert uses “tbs”)

Let’s break it down

Let’s revisit the code. As usual, we start with defining variables in the LET function.

=LAMBDA(ingredient,convert_to,
    LET(
        /*shorter name for ingredient, for simplicity*/
        d,ingredient,

        /*index of the character positions in the ingredient text*/
        idx,SEQUENCE(LEN(d)),

        /*the character array of the ingredient text*/
        chars,MID(d,idx,1),

• d – is just a renaming the longer “ingredient” parameter for brevity.
• idx – is a sequence of integers as long as the number of characters in d – an index.
• chars – is a character array of the characters in d.

        /*the position of the first non-breaking space in the character array
        This appears immediately after the measurement*/
        nbs_pos,XMATCH(160,CODE(chars)),

        /*The characters up to the character before the non-breaking space*/
        up_to_nbs,INDEX(chars,SEQUENCE(nbs_pos-1)),

        /*A lambda to simplify a text join with no delimiter*/
        join,LAMBDA(arr,TEXTJOIN("",TRUE,arr)),

        /*the original measurement of the ingredient text*/
        measurement,join(up_to_nbs),

• nbs_pos – is the position of the first non-breaking space in the character array. We convert the character array to an array of CODEs and then look for the code 160 (non-breaking space).
• up_to_nbs – returns the items from the character array (chars) which precede the position of the non-breaking space.
• join – is a helper lambda function to perform a textjoin with no delimiter. This same operation is performed several times in this function as a whole, so defining it as a lambda here is useful for simplicity later.
• measurement – we use the join lambda defined above to re-join the characters preceding the non-breaking space. In the first row of the table above, this measurement is “300ml”

        /*An array indicating which of the measurement characters are numbers*/
        nums,ISNUMBER(NUMBERVALUE(up_to_nbs)),

        /*An array of the numbers in the measurement*/
        numbers_array,FILTER(up_to_nbs,nums),

        /*An array of the non numbers in the measurement*/
        non_numbers_array,FILTER(up_to_nbs,NOT(nums)),

        /*The value of the measurement*/
        numbers,NUMBERVALUE(join(numbers_array)),

        /*The unit of measure (uom) of the measurement*/
        non_numbers,IFERROR(TRIM(join(non_numbers_array)),""),

• nums – converts the characters in the “up_to_nbs” array ({“3″,”0″,”0″,” “,”m”,”l”} in the example of the first row) to their equivalent NUMBERVALUE if they are numbers and then checks if they are numbers. For the example, the result is then {TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}.
• numbers_array – filters the up_to_nbs array for those elements which have TRUE in the nums array. We receive {“3″,”0″,”0”} in the example.
• non_numbers_array – filters the up_to_nbs array for those elements which don’t have TRUE in the nums array. We receive {” “,”m”,”l”} in the example.
• numbers – re-joins the numbers_array and converts the result to an actual number. The result is then 300.
• non_numbers – re-joins the non_numbers_array and then trims the result so that we have “ml” in the example. Some ingredients won’t have a unit of measure (such as “4 large eggs”). In those cases, this operation returns an error, so we use IFERROR to return an empty string instead.

        /*Some text conversions to ensure we pass the right text to the convert function
        For example, if we pass "oz", we are asking for Fluid ounce
        Similarly, if we want solid ounce, we must pass "ozm"*/
        conversions,{"fl oz","oz";"oz","ozm";"tbsp","tbs"},

        /*A lambda to apply a standardize a uom if there's a conversion available*/
        standardize_uom,LAMBDA(uom,XLOOKUP(uom,INDEX(conversions,,1),INDEX(conversions,,2),uom)),

        /*Try to conver the measurement to the new UoM*/
        converted,CONVERT(numbers,standardize_uom(non_numbers),standardize_uom(convert_to)),

• conversions – is a two-column array of pseudonyms to use for common recipe units of measure. So, if we find “fl oz” in the data (either the ingredient text or the convert_to value), we can lookup the “oz” text, which can be passed to the CONVERT function.
• standardize_uom – is a lambda function that will take a unit of measure and see if it is a pseudonym by using XLOOKUP to search the “conversions” array defined above. If the unit of measure is found in the conversions array in column 1, it returns the value from column 2. If it’s not found, it just returns the original unit of measure. 
• converted – attempts to CONVERT the value (numbers = 300) from the original unit of measure (non_numbers = “ml”) standardized if applicable using the lambda defined above to the new unit of measure (convert_to = “fl oz”), standardized if applicable (in this case, to “oz”). 

        /*Some units of measurement aren't available, so we check if converted is an error
        and if it is, just use the original measurement text*/
        new_measurement,ROUND(IFERROR(converted,numbers),1)&" "&IF(ISERROR(converted),non_numbers,convert_to),

        /*Finally, substitute the old measurement with the new measurement*/
        SUBSTITUTE(d,measurement,new_measurement)
    )
);

• new_measurement – checks if the “converted” variable has produced an error. If it has, then it simply returns the original measurement. If it hasn’t produced an error, it returns the converted measurement, represented as a text value (i.e. “10.1 fl oz”)
• Finally, we use SUBSTITUTE to replace the original measurement in the ingredient text with the new measurement.

In summary

This was a bit of a silly lambda to convert recipe ingredients to different units in Excel.

Nevertheless, we saw how to separate some characters in a text string using a non-breaking space.

We saw how to use helper lambdas to simplify a function that’s used multiple times (TEXTJOIN(“”,TRUE,etc).

We used a typed array (conversions) to provide pseudonyms for allowed conversion arguments for the CONVERT function.

And finally, we used the CONVERT function to convert the original units of measure to a new unit, then replaced the original unit of measure in the ingredient text with the new unit of measure.