1
\$\begingroup\$

The TEXTJOIN function has a really nice interface:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252])

... where text2 onwards are optional args, and each text argument can be a string (hardcoded or function return) or an array of strings (from a range or an array formula). This lets you call the function in a few ways:

=TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.") //comma separated values
=TEXTJOIN(" ", TRUE, A2:A8) //A 1D array
=TEXTJOIN(" ", TRUE, {1,2,3; 4,5,6; 7,8,9}) //2D array
=TEXTJOIN(" ", TRUE, A2:A8, "foo", A1:B3, "bar") //Combo!

Ultimately I want to create a PRINTF LAMBDA function which implements a similar interface, e.g. :

=PRINTF(mask, text1, [text2], …) //mask like "foo {1} bar {2}"

... but LAMBDA functions have no ParamArray type like VBA does to accept a variable number of args, and no ability to loop through their arguments either. Nevertheless, the new [optional] syntax combined with the ISOMITTED function can be used to define LAMBDAs with variable number of arguments so with that this is what I made:

PRINTF:

=LAMBDA(
    mask,
    _0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],
    LET(
        tokensArray,
        FLATARRAY(
            _0, _1, _2, _3, _4, _5, _6, _7, _8, _9, _10, _11, _12, _13, _14, _15, _16, _17, _18, _19, _20, _21, _22, _23, _24, _25, _26, _27, _28, _29, _30, _31, _32, _33, _34, _35, _36, _37, _38, _39, _40, _41, _42, _43, _44, _45, _46, _47, _48, _49, _50, _51, _52, _53, _54, _55, _56, _57, _58, _59, _60, _61, _62, _63, _64, _65, _66, _67, _68, _69, _70, _71, _72, _73, _74, _75, _76, _77, _78, _79, _80, _81, _82, _83, _84, _85, _86, _87, _88, _89, _90, _91, _92, _93, _94, _95, _96, _97, _98, _99, _100, _101, _102, _103, _104, _105, _106, _107, _108, _109, _110, _111, _112, _113, _114, _115, _116, _117, _118
        ),
        escapedResult,
        _ReplaceRecursive(
            mask,
            tokensArray,
            1,
            ROWS(tokensArray)
        ),
        SUBSTITUTE(escapedResult,"\}","}")
    )
)

Yes, some hardcoded arguments - a bit nasty but not too bad. Some points to note:

  • The first argument _0 is mandatory, this is because my FLATARRAY function (I'll get onto that) must return an array with at least one element. Still calling PRINTF(mask) with no argument would be a redundant noop anyway so I think this is an okay API.
  • The number of arguments could be up to 253, however the name manager has a character limit of 2000 for me and some of the LAMBDAS are too long to support more args. I think 100 is more than sufficient anyway (bear in mind each arg could itself be a whole array of values, like in TEXTJOIN).

That PRINTF function relies on a _ReplaceRecursive function to do the legwork:

_ReplaceRecursive:

=LAMBDA(
    mask,
    tokens,
    i,
    tokenCount,
    IF(
        i > tokenCount,
        mask,
        LET(
            token,
            INDEX(tokens,i),
            escapedToken,
            SUBSTITUTE(token,"}","\}"),
            inIndexedMode,
            ISERROR(
                FIND("{}",mask,1)
            ),
            substituted,
            IF(
                inIndexedMode,
                SUBSTITUTE(
                    mask,
                    "{" & i & "}",
                    escapedToken
                ),
                SUBSTITUTE(
                    mask,
                    "{}",
                    escapedToken,
                    1
                )
            ),
            _ReplaceRecursive(
                substituted,
                tokens,
                i + 1,
                tokenCount
            )
        )
    )
)

Some notes:

  • I've gone with an _PascalCase naming system for this private implementation LAMBDA as opposed to YELLCASE for the Excel UDF entry point.
  • The function has 2 modes; "{indexed}" where you provide the 1-based location in the tokensArray of the string you wish to substitute, and {} positional. More explanation over on my stack overflow answer here. It has some basic escaping although it will incorrectly handle the edge case of \} in the mask, not sure if there's an easy fix for this, open to suggestions.

Mimicking ParamArray

The other aspect of the PRINTF function is the argument handling, and this is where things start to get a bit messy...

FLATARRAY:

A helper function for taking an array of arrays and flattening it into a 1D vector.

=LAMBDA(
    _0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],[_119],
    LET(
        indexable,
        LAMBDA(
            myIndex,
            CHOOSE( //this will create an array with default values for omitted args
                myIndex,
                _1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63,_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119
            )
        ),
        argsSize,
        ARGSCOUNT(
            _1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63
        ) +
        ARGSCOUNT( //have to split because of argument limits in ARGSCOUNT()
            _64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119
        ),
        joiner, //a function to merge all the args into a single array using an accumulator
        LAMBDA(
            runningTot,
            x,
            ARRAYJOIN(
                runningTot,
                indexable(x)
            )
        ),
        base,
        FLATTEN(_0), //flatten the first arg so its dimensions are still consistently 1D when it's the only arg
        IF(
            argsSize = 0,
            base,
            REDUCE(
                base,
                SEQUENCE(argsSize),
                joiner
            )
        )
    )

The principle here is that CHOOSE(SEQUENCE(n), arg_1, arg_2 ... arg_n) constructs an array with n items. However we don't know the value of n, so instead I use CHOOSE(SEQUENCE(m), arg_1, arg_2, ... arg_n) with

  • n optional arguments to my LAMBDA
  • m actually supplied (e.g. TEXTJOIN has n = 252, but I can call it passing m = 3 items to join)
  • m<=n

So how do you evaluate m the number of arguments passed? That is the subject of a question I asked on SO: Create dynamic array from non-contiguous cells/values without VBA but what I ultimately went for was a hardcoded binary search to find the index of the first omitted argument without checking all of them (this works because if the 6th argument is omitted, I know all the arguments from 7 onwards will be as well). This is the resulting function:

=LAMBDA([p_1],[p_2],[p_3],[p_4],[p_5],[p_6],[p_7],[p_8],[p_9],[p_10],[p_11],[p_12],[p_13],[p_14],[p_15],[p_16],[p_17],[p_18],[p_19],[p_20],[p_21],[p_22],[p_23],[p_24],[p_25],[p_26],[p_27],[p_28],[p_29],[p_30],[p_31],[p_32],[p_33],[p_34],[p_35],[p_36],[p_37],[p_38],[p_39],[p_40],[p_41],[p_42],[p_43],[p_44],[p_45],[p_46],[p_47],[p_48],[p_49],[p_50],[p_51],[p_52],[p_53],[p_54],[p_55],[p_56],[p_57],[p_58],[p_59],[p_60],[p_61],[p_62],[p_63],IF(ISOMITTED(p_32),IF(ISOMITTED(p_16),IF(ISOMITTED(p_8),IF(ISOMITTED(p_4),IF(ISOMITTED(p_2),IF(ISOMITTED(p_1),0,1),IF(ISOMITTED(p_3),2,3)),IF(ISOMITTED(p_6),IF(ISOMITTED(p_5),4,5),IF(ISOMITTED(p_7),6,7))),IF(ISOMITTED(p_12),IF(ISOMITTED(p_10),IF(ISOMITTED(p_9),8,9),IF(ISOMITTED(p_11),10,11)),IF(ISOMITTED(p_14),IF(ISOMITTED(p_13),12,13),IF(ISOMITTED(p_15),14,15)))),IF(ISOMITTED(p_24),IF(ISOMITTED(p_20),IF(ISOMITTED(p_18),IF(ISOMITTED(p_17),16,17),IF(ISOMITTED(p_19),18,19)),IF(ISOMITTED(p_22),IF(ISOMITTED(p_21),20,21),IF(ISOMITTED(p_23),22,23))),IF(ISOMITTED(p_28),IF(ISOMITTED(p_26),IF(ISOMITTED(p_25),24,25),IF(ISOMITTED(p_27),26,27)),IF(ISOMITTED(p_30),IF(ISOMITTED(p_29),28,29),IF(ISOMITTED(p_31),30,31))))),IF(ISOMITTED(p_48),IF(ISOMITTED(p_40),IF(ISOMITTED(p_36),IF(ISOMITTED(p_34),IF(ISOMITTED(p_33),32,33),IF(ISOMITTED(p_35),34,35)),IF(ISOMITTED(p_38),IF(ISOMITTED(p_37),36,37),IF(ISOMITTED(p_39),38,39))),IF(ISOMITTED(p_44),IF(ISOMITTED(p_42),IF(ISOMITTED(p_41),40,41),IF(ISOMITTED(p_43),42,43)),IF(ISOMITTED(p_46),IF(ISOMITTED(p_45),44,45),IF(ISOMITTED(p_47),46,47)))),IF(ISOMITTED(p_56),IF(ISOMITTED(p_52),IF(ISOMITTED(p_50),IF(ISOMITTED(p_49),48,49),IF(ISOMITTED(p_51),50,51)),IF(ISOMITTED(p_54),IF(ISOMITTED(p_53),52,53),IF(ISOMITTED(p_55),54,55))),IF(ISOMITTED(p_60),IF(ISOMITTED(p_58),IF(ISOMITTED(p_57),56,57),IF(ISOMITTED(p_59),58,59)),IF(ISOMITTED(p_62),IF(ISOMITTED(p_61),60,61),IF(ISOMITTED(p_63),62,63)))))))

... or for a more manageable number of arguments:

=LAMBDA(
    [_1],[_2],[_3],[_4],[_5],[_6],[_7],
    IF(
        ISOMITTED(_4),
        IF(
            ISOMITTED(_2),
            IF(
                ISOMITTED(_1),
                0,
                1
            ),
            IF(
                ISOMITTED(_3),
                2,
                3
            )
        ),
        IF(
            ISOMITTED(_6),
            IF(
                ISOMITTED(_5),
                4,
                5
            ),
            IF(
                ISOMITTED(_7),
                6,
                7
            )
        )
    )
) 

Hopefully you can see how that decision tree cuts down on the number of arguments that have to be checked with ISOMITTED(). This function was hit hard by the character limit so is limited to only 63 args (it has to be a power of two - 1 for the binary tree to be symmetric, which is easier to programme)

And finally, there are a couple of helper functions:

FLATTEN:

Convert 2D array to 1D array

=LAMBDA(
    array,
    LET(
        r, ROWS(array),
        c, COLUMNS(array),
        INDEX(
            array,
            ROUNDUP(
                SEQUENCE(
                    r * c
                ) / c,
                0
            ),
            MOD(
                SEQUENCE(
                    r * c,
                    ,
                    0
                ),
                c
            ) + 1
        )
    )
)

(source)

ARRAYJOIN

Merge two arrays into a single 1D array - like a For...Each over both ranges in a row...

=LAMBDA(
    a,
    b,
    LET(
        flatA, FLATTEN(a),
        flatB, FLATTEN(b),
        sizeA, ROWS(flatA),
        sizeB, ROWS(flatB),
        joinedIndexable,
        LAMBDA(
            x,
            IF(
                x <= sizeA,
                INDEX(
                    flatA,
                    x
                ),
                INDEX(
                    flatB,
                    x - sizeA
                )
            )
        ),
        joinedIndexable(
            SEQUENCE(
                sizeA + sizeB
            )
        )
    )
)

For contrast, the equivalent VBA (without flattening) looks like:

Public Function ARRAYFROMARGS(ParamArray args()) As Variant
    ARRAYFROMARGS = args
End Function

That looks really bad I know, but actually now I have those helper functions, implementing ParamArray in another lambda is as simple as defining n optional args and passing them to FLATARRAY and it can filter out the omitted ones automatically. I also have a simpler ARRAY function which does not flatten the args (so same as the VBA), but that doesn't match the TEXTJOIN behaviour I'm after. Code for that is here.

\$\endgroup\$
1
\$\begingroup\$

Very nice! It can be simplified, since you don't need _ReplaceRecursive. I subbed in a REDUCE like this and this and it seems ok, assuming we don't have to escape the tokens.

       REDUCE(
            mask,
            SEQUENCE(ROWS(tokensArray)),
            LAMBDA(acc,i,
                SUBSTITUTE(
                    acc,
                    "{" & i & "}",
                    INDEX(tokensArray,i)
                )
            )
        )
\$\endgroup\$
2
  • 1
    \$\begingroup\$ Thanks:) Yeah wrote the Printf function itself before Reduce was announced but I was thinking it simplifies things a little (didn't want to modify the question though). The escaping lets you use PRINTF("mask {1}, {2}", "foo {2}", "bar") i.e. a {} placeholder in a substitute token. Without escaping yo get "mask foo bar, bar" not "mask foo {2}, bar". However with this approach you can't have \} intentionally in the mask as it will become } which is an edge case, can't think of an easy workaround though. \$\endgroup\$
    – Greedo
    Oct 3 at 13:08
  • \$\begingroup\$ Ahh, I see, thanks for the clarification! \$\endgroup\$
    – TB__
    Oct 3 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.