The gist for this lambda function can be found here.

The goal

Here’s a picture of a red velvet cake I baked last week. 

I baked this cake from scratch for my son’s birthday. I’m not a baker. But it makes a nice change from working with computers. 

Because I’m English, I get most of my recipes from BBC Good Food. The recipe I used is here.

I live in the US. This means most of my ingredients have the wrong units of measure which don’t necessarily fit with recipes published by the BBC and my measuring instruments are wrong misaligned as well.

After the cake was finished, I happened to stumble across Excel’s CONVERT function. And so, in this post I will try to:

Create a lambda function to convert a recipe to different units of measure

A solution

The recipe for this particular cake is:

Ingredient text Text before non-breaking space
300ml vegetable oil, plus extra for the tins 300ml
500g plain flour 500g
2 tbsp cocoa powder 2 tbsp
4 tsp baking powder 4 tsp
2 tsp bicarbonate of soda 2 tsp
560g light brown soft sugar 560g
1 tsp fine salt 1 tsp
400ml buttermilk 400ml
4 tsp vanilla extract 4 tsp
30ml red food colouring gel or about ¼ tsp food colouring paste, (use a professional food colouring paste if you can, a natural liquid colouring won’t work and may turn the sponge green) 30ml
4 large eggs 4
Fortunately for me, this website has a non-breaking space between what I’ll call the measurement (e.g. 300ml) and the description (e.g. “vegetable oil, plus extra for the tins”). This is true for all of the recipe ingredients. This is convenient because it lets us separate the measurement from the description somewhat simply.

So, here’s a solution for this particular recipe and hopefully for other recipes which have this same convenient separation. I call this CONVERTRECIPE:

=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),

        /*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),

        /*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)),""),

        /*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)),

        /*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)
    )
);

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”)
If you’d like to use the function or modify it for your own needs, please go to the gist link at the top of this post. If you’d like to read about how it works, read on!

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.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>