3
\$\begingroup\$

I have created a named function with signature PadInternal(base, width, paddingStr) where:

  • base is a string you want to add padding to
  • width is the length of the individual chunks
  • paddingStr is the string to pad with

called from a cell like: Calling from cell

PadInternal("Hello", 1, " ") = "H e l l o"
PadInternal("World", 3, "**") = "Wor**ld"

And here's the function:

Tag Description
Name PadInternal
Scope Workbook
Comment base : a string you want to add padding to | width : the length of the individual chunks | paddingStr : the string to pad with
Refers To =LAMBDA(base,width,paddingStr, IF(LEN(base)<=width, base, LET(LHS, LEFT(base, width), RHS, RIGHT(base, LEN(base) - width), LHS & paddingStr & PadInternal(RHS,width,paddingStr))))
=LAMBDA(
    base,
    width,
    paddingStr,
    IF(
        LEN(
            base
        ) <= width,
        base,
        LET(
            LHS,
            LEFT(
                base,
                width
            ),
            RHS,
            RIGHT(
                base,
                LEN(
                    base
                ) - width
            ),
            LHS & paddingStr &
            PadInternal(
                RHS,
                width,
                paddingStr
            )
        )
    )
)

Questions

As this is my first time using recursive Lambda functions in Excel, I'd like some feedback. In particular:

  • Is my algorithm efficient - I was thinking something with TEXTJOIN may be faster?
  • How could this be improved to take a dynamic array as "base"?
  • Can I have default values for the arguments?
  • What about meta data (formatting of the tooltip, scope etc), are there other ways to make my function more accessible?
\$\endgroup\$
4
  • \$\begingroup\$ PS, for a bit of context, I'm using this to format hexedecimal strings nicely with 2 character chunks \$\endgroup\$
    – Greedo
    Dec 16 '20 at 19:26
  • \$\begingroup\$ Edit your post instead of commenting. \$\endgroup\$
    – Tom Gebel
    Dec 16 '20 at 19:32
  • 1
    \$\begingroup\$ @TomGebel normally I would, but for this I think it's enough to review the function in its own right and the use case is not actually that relevant to the question - I don't want to tie the function to a particular use case \$\endgroup\$
    – Greedo
    Dec 16 '20 at 19:41
  • 1
    \$\begingroup\$ I haven't used the LAMBDA function quite yet, and this looks interesting. I'd change your function name though, something like ChunkMyText might do. :) \$\endgroup\$
    – PeterT
    Dec 17 '20 at 20:01
2
\$\begingroup\$

I thought about this for a while and came up with some (what I think are) improvements:

=LAMBDA(base,width,paddingStr,TEXTJOIN(paddingStr,,MID(base,SEQUENCE((LEN(base)/width)+1,,1,width),width)))

This would:

  • No longer require a recursive LAMBDA() which should prove to be faster (untested)
  • Uses TEXTJOIN() which can handle errors and empty values internally so there is no longer any worry about that.
  • The use of MID() negates the use of LEFT(), RIGHT() etc.
  • No longer be using LET() which may also be using internal memory.
\$\endgroup\$
1
  • 1
    \$\begingroup\$ With the risk of appearing pedantic, a slight improvement would be to replace the +1 with +0.5 so that when the length is an even number the sequence does not generate an extra index which would be larger than the length thus resulting in an extra blank string to be joined. Nice solution! \$\endgroup\$ Jan 6 '21 at 15:21

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.