ExcelFunctions

Excel LAMBDAs (Custom Functions)

I'll list some of my LAMBDAs that may or may not be useful to other people. Typically trivial, nothing ground breaking.

Refer to Microsoft's LAMBDA announcement and documentation for a full explanation of LAMBDA functions.

TLDR: Add the functions to the Name Manager (Ribbon > Formulas > Defined Names > Name Manager, or Ctrl + F3).

Example

Name: IsNull
Refers to: =LAMBDA(VALUE,LEN(VALUE)=0)

Between(VALUE,LOWER,UPPER)

Checks if numeric value is between an UPPER and LOWER bound.

=LAMBDA(VALUE,LOWER,UPPER,AND(VALUE>=LOWER,VALUE<=UPPER))

Examples:

=Between(10,5,15)
Return value: TRUE

=Between(10,20,30)
Return value: FALSE

CleanFileName(FILENAME)

Strips illegal characters from a filename, as documented in Microsoft's Naming Files, Paths, and Namespaces. It expects a file name only, this will strip slashes, so concatenate the path later.

=LAMBDA(FILENAME,REDUCE(FILENAME,HSTACK(CHAR(SEQUENCE(,30,,1)),{"<",">",":","""","/","\","|","?","*"}),LAMBDA(NAME,CHAR,SUBSTITUTE(NAME,CHAR,""))))

Examples:

=CleanFileName("Bad> *File* Name!")
Return value: Bad File Name!

GetDigits(TEXT)

Strips all non-numeric characters from text

=LAMBDA(VALUE,REDUCE(VALUE,CHAR(HSTACK(SEQUENCE(,47,1),SEQUENCE(,127-58,58))),LAMBDA(TXT,CHR,SUBSTITUTE(TXT,CHR,""))))

Examples:

=GetDigits("Invoice 2025-01-01.xlsx")
Return value: 20250101

GetFirst(ARRAY)

Returns first non-zero length value in ARRAY. If you need to pass discrete values, use VSTACK().

=LAMBDA(ARRAY,CHOOSEROWS(TOCOL(ARRAY,1),1))

Examples:

=GetFirst({"","","Hello","World"})
Return value: Hello
=GetFirst(VSTACK(A1,A3,A5,B2,B4))

InArray(ARRAY, VALUE)

Checks if ARRAY contains VALUE.

=LAMBDA(VALUE,ARRAY,ISNUMBER(MATCH(VALUE,ARRAY,0)))

Examples:

=InArray({1,2,3,4,5}, 3)
Return value: TRUE

=InArray({1,2,3,4,5}, 10)
Return value: FALSE

IsNull(VALUE)

Checks if VALUE has zero length.

=LAMBDA(VALUE,LEN(VALUE)=0)

Examples:

=IsNull("Hello")
Return value: FALSE

=IsNull("")
Return value: TRUE

=IsNull(0)
Return value: FALSE

NetBookValue(AcquisitionValue,CapitalisationDate,UsefulLife,NBVDate)

Calculates Net Book Value, assuming straight line depreciation.

=LAMBDA(AcquisitionValue,CapitalisationDate,UsefulLife,NBVDate,MAX(0,AcquisitionValue-(NBVDate-CapitalisationDate)/365*(AcquisitionValue/UsefulLife)))

Pad(Value,Width,[Cut])

Makes a value fixed width by padding with trailing spaces.

If the string length of Value exceeds Width, and optional Cut parameter is TRUE, only the first WIDTH characters will be retuned. Otherwise, an NA error will be thrown.

=LAMBDA(Value,Width,[Cut],
    IF(LEN(Value)>Width,
        IF(Cut,
            LEFT(Value,Width),
            NA()
        ),
        Value&REPT(" ",Width-LEN(Value))
    )
)

Note:

TEXTJOIN and CONCAT do not play nice with SPILL ARRAYS, hence the use of the & (ampersand) concatenation operator in this instance.

Example:

(Spaces shown as # for sake of clarity)
=Pad("Hello",10)
Return value: Hello#####
=Pad("Hello",5)
Return value: Hello
=Pad("Hello",2)
Return value: #N/A
=Pad("Hello",2,TRUE)
Return value: He