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!
TEXTJOIN is an example of a variadic function - one which can accept any number of arguments - and in VBA we have the ParamArray modifier to create variadic functions. Here however, I wanted to create a PRINTF LAMBDA function which implements a similar interface without using VBA, 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
_0is mandatory, this is because myFLATARRAYfunction (I'll get onto that) must return an array with at least one element. Still callingPRINTF(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
noptional arguments to my LAMBDAmactually 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.
