Skip to content

Instantly share code, notes, and snippets.

@ncalm
Created August 25, 2023 15:53
Show Gist options
  • Save ncalm/629adef666f8f4bd7054f3bc5c71d60a to your computer and use it in GitHub Desktop.
Save ncalm/629adef666f8f4bd7054f3bc5c71d60a to your computer and use it in GitHub Desktop.
This Excel LAMBDA function provides a FORMULATEXT capability for Python in a Python cell
/*
Provides a FORMULATEXT capability for Python cells
pythonCell: A reference to a Python cell
[asArray]: A TRUE/FALSE value indicating whether
or not to spill the results of the formula
default is TRUE
*/
FORMULATEXT.PY = LAMBDA(pythonCell,[asArray],
LET(
_asArray,IF(ISOMITTED(asArray),TRUE,asArray),
step0, FORMULATEXT(pythonCell),
step1, TEXTAFTER(step0,"PY("""),
step2, LEFT(step1,LEN(step1)-4),
step2Arr, TEXTSPLIT(step2,,CHAR(10)),
IF(_asArray,step2Arr,step2)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment