Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions LAMBDA RegEx Formula Prompter

Last active April 6, 2025 07:37
Show Gist options
  • Save CHatmaker/cfea5c2804a278ca9b620742e7128b2f to your computer and use it in GitHub Desktop.
Save CHatmaker/cfea5c2804a278ca9b620742e7128b2f to your computer and use it in GitHub Desktop.
Download ZIP
When learners are entering formulas in instructional workbooks, RXFormulaPromptλ( Prompt, Formula, Entry), tells the learner what to enter and guides them in correcting errors.
Raw
BXL 5g Functions LAMBDA RegEx Formula Prompter
/* A collection of 5g Functions for creating classroom exercises that prompt
learners during formula entry. Suggested module name: RXF
*/
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Dec 16 2024 Craig Hatmaker Copyright
Dec 18 2024 Craig Hatmaker Added IsCommentλ, DisplayλLinesλ, and DisplayλPromptsλ
Jan 02 2025 Craig Hatmaker See FormulaSplitλ
Jan 05 2025 Craig Hatmaker See DisplayEntriesλ
Jan 18 2025 Craig Hatmaker Removed RXF. references
Feb 04 2025 Craig Hatmaker Added AreRefsSameλ
*/
Aboutλ =
LET(Group1, TRIM(TEXTSPLIT(
"About: →A collection of 5g Functions for creating classroom exercises that prompt¶" &
"→learners during formula entry. Suggested module name: RXF¶" &
"Version: →Feb 04 2025¶" &
"Gist URL: →https://gist.github.com/CHatmaker/cfea5c2804a278ca9b620742e7128b2f ¶" &
"Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"Functions →Description¶" &
"Aboutλ →Produces this table¶" &
"FormulaPromptλ →Prompt learner to enter formula, check answer, provide feedback¶" &
"Promptλ →Prompt learner to enter a non-formula, check answer, provide feedback¶" &
"DisplayEntriesλ →Creates a dynamic array of learner entries with comment lines and LET step names¶" &
"DisplayPromptsλ →Creates a dynamic array of Formula Prompts with comment lines¶" &
"→¶" &
"Prompt Support→¶" &
"FormulaSplitλ →Split a formula into an array of references, functions, operators, and various bracket types¶" &
"RXEscapeSpecCharsλ →Apply escape characters where characters in a string match RegEx special characters¶" &
"Formula2RXλ →Convert a formula into a regular expression pattern¶" &
"RX2Formulaλ →Revert a RegEx version of a formula to a normal formula¶" &
"CreateSolutionsλ →Create a dynamic array of solutions without LAMBDA or LET step names",
"→","¶"
)),
Group2, TRIM(TEXTSPLIT(
"'Is' functions→¶" &
"IsArrayλ →Is string enclosed in curly brackets¶" &
"IsBracketedλ →Is string enclosed in square brackets¶" &
"IsCellRefλ →Is string a single cell reference¶" &
"IsCommentλ →Is string a LAMBDA comment¶" &
"IsFunctionλ →Is string a function¶" &
"IsQuotedλ →Is string enclosed in double quotes¶" &
"IsRangeRefλ →Is string a range reference¶" &
"IsStrucRefλ →Is string a structured reference¶" &
"AreRefsSameλ →Determine if two reference strings are the same range¶" &
"→¶" &
"'Get' functions→¶" &
"GetFunctionsλ →Extract functions from a formula¶" &
"GetOprsλ →Extract operators from a formula¶" &
"→¶" &
"Literals →Description - Regular Expressions for finding...¶" &
"RegExChars →Characters special inside regular expressions¶" &
"Operators →Excel formula operators¶" &
"BracketsAndCommas →Parentheses, square brackets, curly brackets, and commas¶" &
"FunctionExclude →Characters that formulas cannot contain anywhere¶" &
"FunctionExcludeStart →Characters that formulas cannot start with¶" &
"Function →Functions in a formula¶" &
"SheetRef →The portion of a reference from a different sheet that identifies the sheet¶" &
"SingleCellRef →Single cell address (example A1, $A1, A$1, or $A$1) in a formula¶" &
"ColumnRef →Column reference (example A:A) in a formula¶" &
"RowRef →Row reference (example 1:1) in a formula¶" &
"RangeRef →Range reference (example A1:B1, etc.) in a formula¶" &
"CellRef →Either of Single cell, column, row, or range references in a formula¶" &
"StructuredRef →Structured reference (example Table1[Column]) in a formula¶" &
"LAMBDAStart →LAMBDA function start¶" &
"LAMBDALETStep →LAMBDA LET step¶" &
"LAMBDAComment →LAMBDA comment¶" &
"Array →String enclosed in curly brackets¶" &
"QuotedString →String enclosed in double quotes",
"→","¶"
)),
Result, VSTACK(Group1, {"",""}, Group2),
Result
);
// Named Literals
/** Special characters used as operators in Regular Expressions*/
RegExChars = "[\\\^\$\.\|\?\*\+\(\)\{\}\[\]]";
/** Excel's operators*/
Operators = "(<=|>=|<>|[\+\-\*\/\=\<\>\^\&])";
/** Excel's brackets and separators*/
BracketsAndCommas = "[\[\]\(\)\{\}\,\;]";
//https://www.linkedin.com/feed/update/urn:li:activity:7281414089476579329?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7281414089476579329%2C7281482277191106560%29&dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287281482277191106560%2Curn%3Ali%3Aactivity%3A7281414089476579329%29
/** RegEx pattern for anything between normal brackets*/
BetweenBrackets = "(?<=\p{Ps}).+?(?=\p{Pe})";
// Functions
/** Illegal characters anywhere in function names */
FunctionExclude = "•‼\s!""#$%'\(\)\*\+,-/:;<=>@\[\]\^'{|}~`¢£¥¬«»";
/** Illegal first characters in function names */
FunctionExcludeStart = "☺☻♦◘◙♫☼►◄▬↨.0123456789⌂₧⌐░▐▀⌠⌡∙";
/** RegEx pattern for extracting legal function names from a string */
Function = "[^" & FunctionExcludeStart & FunctionExclude & "]{1}[^" & FunctionExclude & "]*\(";
// References
/** RegEx pattern for extracting sheet names from Excel cell references */
SheetRef = "('?.*'?!)";
/** RegEx pattern for extracting single cell references from Strings */
SingleCellRef = SheetRef & "?(\$?[A-Za-z]{1,3})(\$?[0-9]{1,7}#?)";
/** RegEx pattern for extracting column references from Strings */
ColumnRef = SheetRef & "?\$?[A-Za-z]{1,3}:\$?[A-Za-z]{1,3}";
/** RegEx pattern for extracting row references from Strings */
RowRef = SheetRef & "?\$?\d{1,7}:\$?\d{1,7}";
/** RegEx pattern for extracting range (multi-cell) references from Strings */
RangeRef = SheetRef & "?(\$?[A-Za-z]{1,3}\$?\d{1,7}#?)(:|.:|.:.|:.)(\$?[A-Za-z]{1,3}\$?\d{1,7}#?)";
/** RegEx pattern for extracting cell addresses of anytype from Strings */
CellRef = SheetRef & "?(" & SingleCellRef & "|" & ColumnRef & "|" & RowRef & "|" & RangeRef & ")";
/** RegEx pattern for extracting Structured References from Strings */
StructuredRef = "([A-Za-z_\\][A-Za-z0-9._]*(\[{1,2}(\#ALL|\#Headers|\#Data|\#Totals|\#This Row)\] \,)?)?(((\[{1,2}@\[)|(\[@)|\[{1,2})([^\[])+\])(:(\[)([^\[])+\])?";
// LAMBDA sections
/** RegEx pattern for extracting LAMBDA start from formulas */
LAMBDAStart = "^\w=LAMBDA\(.*";
/** RegEx pattern for extracting LET steps from formulas */
LAMBDALETStep = "^\w\,.*";
/** RegEx pattern for extracting comments from LAMBDAs. Does not handle comment lines between frist and last comment line */
LAMBDAComment = "(//|/\*|/\*\*).*";
// Groupings
/** RegEx pattern for identifying arrays in formulas */
Array = "{.*?}";
/** RegEx pattern for identifying quoted values in formulas */
QuotedString = """(.*?)""";
/* FUNCTION NAME: FormulaPromptλ
DESCRIPTION:*//**Prompt learner to enter formula, check answer, provide feedback*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
FormulaPromptλ = LAMBDA(
// Parameter Declarations
[Prompt],
[Formula],
[Entry],
[Concise],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →FormulaPromptλ( Prompt, Formula, Entry, [Concise])¶" &
"DESCRIPTION: →Prompt learner to enter formula, check answer, provide feedback¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 06 2024¶" &
"PARAMETERS: →¶" &
"Prompt →(Required) Tells learner what they should do.¶" &
"Formula →(Required) Formula that learner must produce.¶" &
"Entry →(Required) Cell in which the learner types their answer.¶" &
"Concise →(Optional) Default is FALSE. TRUE=Drop ""? reveals answer""¶" &
"EXAMPLES: →Formula¶" &
"→=FormulaPromptλ( ""Create a row of numbers from 1 to 10"",¶" &
"→=""SEQUENCE( , 10)"", Sheet1!$C$2)",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Prompt),
ISOMITTED( Formula)
),
// Set Defaults
Concise, IF( ISOMITTED( Concise), FALSE, Concise),
Entry, IF( ISFORMULA( Entry), FORMULATEXT( Entry), Entry),
Formula, RegExReplace( Formula, "[\r\n]", ""),
// Procedure
Result,
SWITCH( Entry,
0, Prompt & IF( Concise, "", " ""?"" reveals answer"),
"?", Formula,
Let(
Split, IFERROR(FormulaSplitλ(Formula), Formula),
RXArray, MAP( Split, RXEscapeSpecCharsλ),
Count, COUNTA( RXArray),
Counter, SEQUENCE( Count),
Response, REDUCE("^", Counter,
LAMBDA( Acc, n,
IF( LEFT(Acc, 1) = "!",
Acc,
LET(
FormulaElement, INDEX(RXArray, n),
FormulaStub, Acc & FormulaElement ,
ErrorFound?, NOT( RegExTest( Entry, FormulaStub & IF( n = Count, "$",""), 1)),
IsFunction, IsFunctionλ(FormulaElement),
ErrMessage, IF( ErrorFound?,
SWITCH( n,
1, IF( XOR(LEFT( Formula, 1) = "=", Left(Entry, 1) = "="),
IF( Left(Entry, 1) = "=",
"! Do not begin with equal sign",
"! Begin with an equals sign."
),
"! Start with " & RX2Formulaλ( FormulaStub)
),
Count, IF( XOR(RIGHT( Formula, 1) = ",", Right(Entry, 1) = ","),
IF( Right(Entry, 1) = ",",
"! Do not end with a comma",
"! End with a comma."
),
"! End with " & RX2Formulaλ( FormulaStub)
),
SWITCH( FormulaElement,
",\s*", "! Add a comma after: " & RX2Formulaλ( Acc),
"\)\s*", "! Close the formula with "")"" after: " & RX2Formulaλ( Acc),
"! Type " & IF( IsFunction, "Function", "") & ": " & RX2Formulaλ( FormulaElement) & " after:" & RX2Formulaλ( Acc)
)
),
FormulaStub
),
ErrMessage
)
)
)
),
IF( LEFT( Response, 1) = "!", Response, "Correct!")
)
),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: Promptλ
DESCRIPTION:*//**Prompt learner to enter a non-formula, check answer, provide feedback*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
Promptλ = LAMBDA(
// Parameter Declarations
[Prompt],
[Solution],
[Entry],
[Concise],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Promptλ( Prompt, Formula, Entry, [Concise])¶" &
"DESCRIPTION: →Prompt learner to enter a non-formula, check answer, provide feedback¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 07 2024¶" &
"PARAMETERS: →¶" &
"Prompt →(Required) Tells learner what they should do.¶" &
"Solution →(Required) What learner must produce.¶" &
"Entry →(Required) Cell in which the learner types their answer.¶" &
"Concise →(Optional) TRUE=Drop ""? reveals answer"". Default is FALSE¶" &
"EXAMPLES: →Formula¶" &
"→=Promptλ( ""Close LET() function"","")"", Sheet1!$C$2))",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Prompt),
ISOMITTED( Solution)
),
// Set Defaults
Concise, IF( ISOMITTED( Concise), FALSE, Concise),
Answer, Trim(RegExReplace( Solution, "[\r\n]", "")),
// Procedure
Result, SWITCH( Entry,
0, Prompt & IF( Concise, "", " ""?"" reveals answer"),
"?", Solution,
Solution, "Correct!",
"Enter " & Solution
),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: DisplayEntriesλ
DESCRIPTION:*//**Creates a dynamic array of learner entries with comment lines and LET step names*/
/* REVISIONS: Date Developer Description
Dec 18 2024 Craig Hatmaker Copyright
Jan 04 2024 Craig Hatmaker Removed leading spaces from names
*/
DisplayEntriesλ = LAMBDA([Entries], [Prompts], [Solutions],[IndentSize],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →DisplayEntriesλ( Entries, Prompts, Prefixes, Solutions, [IndentSize])¶" &
"DESCRIPTION: →Creates a dynamic array of learner entries with comment lines and LET step names¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"Entries →(Required) The first cell where formula entries are made.¶" &
"Prompts →(Required) An array of LAMBDA formula Prompts¶" &
"Solutions →(Required) An array of entry solutions¶" &
"IndentSize →(Optional) Nunmber of spaces to indent. Default is 4¶" &
"EXAMPLES: →",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Entries),
ISOMITTED( Prompts),
ISOMITTED( Solutions)
),
IndentSize, IF( ISOMITTED(IndentSize), 4, IndentSize),
Entries, OFFSET(TAKE(Entries, 1, 1), 0, 0, ROWS( Prompts), 1),
// Procedure
Levels, SCAN( 0, Solutions,
LAMBDA( Acc, VAL,
Acc +
(LEN(Val) - LEN((SUBSTITUTE(Val,"(","")))) -
(LEN(Val) - LEN((SUBSTITUTE(Val,")",""))))
)
),
Names, MAP( Levels, REGEXREPLACE( Solutions, "^\s*", ""),
LAMBDA( Lvl, Sol,
IF( AND( LvL = 1, REGEXTEST(Sol, ".*=\s*LAMBDA")),
TRIM( TEXTBEFORE( Sol, "=")),
IF( AND( LVL > 1, REGEXTEST( Sol, "^.*\,.*"), LEFT( Sol, 1) <> "/"),
TEXTBEFORE( Sol, ",") & ",",
""
)
)
)
),
Solutions, MAP( Names, Solutions, LAMBDA( Name, Sol, TEXTAFTER( Sol , Name))),
Indents, DROP( VSTACK( 0, Levels), -1),
Result, MAP( Entries, Prompts, Names, Solutions, Indents,
LAMBDA( Entry, Prompt, Names, Solution, Indent,
REPT( " ", MAX(0, (Indent - IsCommentλ(Solution)) * IndentSize)) &
IF( Prompt = "",
Solution,
IF(Entry = "",
"",
IF( Names = "",
TEXT(Entry, "#"),
TRIM(Names) & " " & Entry
)
)
)
)
),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: CreateSolutionsλ
DESCRIPTION:*//**Create a dynamic array of solutions without LAMBDA or LET step names*/
/* REVISIONS: Date Developer Description
Dec 18 2024 Craig Hatmaker Copyright
*/
CreateSolutionsλ = LAMBDA([Solutions],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →CreateSolutionsλ( Solutions)¶" &
"DESCRIPTION: →Create a dynamic array of solutions without LAMBDA or LET step names¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"Solutions →(Required) An array of entry solutions. Usually LAMBDA text¶" &
"EXAMPLES: →",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Solutions),
// Procedure
Levels, SCAN( 0, Solutions,
LAMBDA( Acc, VAL,
Acc +
IF( IsCommentλ(Val),
0,
(LEN(Val) - LEN((SUBSTITUTE(Val,"(","")))) -
(LEN(Val) - LEN((SUBSTITUTE(Val,")",""))))
)
)
),
Names, MAP( Levels, Solutions,
LAMBDA( Lvl, Sol,
IF( AND( LvL = 1, REGEXTEST(Sol, ".*=\s*LAMBDA")),
TRIM( TEXTBEFORE( Sol, "=")),
IF( AND( LVL > 1, REGEXTEST( Sol, "^.*\,.*"), LEFT( Sol, 1) <> "/"),
TEXTBEFORE( Sol, ",") & ",",
""
)
)
)
),
Solutions, MAP( Names, Solutions,
LAMBDA( Name, Sol,
TRIM(TEXTAFTER( Sol & " ", Name))
)
),
Result, Solutions,
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: DisplayPromptsλ
DESCRIPTION:*//**Creates a dynamic array of formula prompts with comment lines*/
/* REVISIONS: Date Developer Description
Dec 18 2024 Craig Hatmaker Copyright
*/
DisplayPromptsλ = LAMBDA([Prompts], [Solutions], [Entries],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →DisplayPromptsλ( Prompts, Solutions, Entries)¶" &
"DESCRIPTION: →Creates a dynamic array of formula prompts with comment lines¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"Prompts →(Required) An array of formula entry prompts when entry is empty¶" &
"Solutions →(Required) An array of target formulas¶" &
"Entries →(Required) The first cell where entries are made¶" &
"EXAMPLES: →",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Prompts),
ISOMITTED( Solutions),
ISOMITTED( Entries)
),
Entries, OFFSET(TAKE(Entries, 1, 1), 0, 0, ROWS( Prompts), 1),
Solutions, CreateSolutionsλ( Solutions),
// Procedure
Result, MAP( Prompts, Solutions, Entries,
LAMBDA( Prompt, Solution, Entry,
IF( Solution = "",
Prompt,
IF( Prompt= "",
"",
FormulaPromptλ( Prompt, Solution, Entry, TRUE)
)
)
)
),
// Return Result
IF( Help?, Help, Result)
)
);
// Prompt Support routines
/* FUNCTION NAME: FormulaSplitλ
DESCRIPTION:*//**Split a formula into an array of references, functions, operators, and various bracket types*/
/* REVISIONS: Date Developer Description
Dec 19 2024 Craig Hatmaker Copyright
*/
FormulaSplitλ = LAMBDA(
// Parameter Declarations
[Formula],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →FormulaSplitλ( Formula)¶" &
"DESCRIPTION: →Split a formula into an array of references, functions, ¶" &
"→operators, and various bracket types¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 19 2024¶" &
"PARAMETERS: →¶" &
"Formula →(Required) Formula to split into an array¶" &
"EXAMPLES: →Formula¶" &
"Formula →FormulaSplitλ(""=SCAN(0,$D$81#, SUM)""))¶" &
"Result →{=,SCAN(,0,"","",$D$81#,"","",SUM}",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Formula),
// Procedure
RXArray, REGEXEXTRACT( Formula, "(" &
QuotedString & "|" & Function & "|" & Operators & "|" &
CellRef & "|" & BracketsAndCommas & "|" & "[A-Za-z0-9%_.\$#""λ]*" &
")",1),
Result, FILTER( RXArray, RXArray <> ""),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: RXEscapeSpecCharsλ
DESCRIPTION:*//**Apply escape characters where characters in a string match RegEx special characters*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
RXEscapeSpecCharsλ = LAMBDA(
// Parameter Declarations
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RXEscapeSpecCharsλ( String)¶" &
"DESCRIPTION: →Apply escape characters where characters in a string match¶" &
"→RegEx special characters and append /s* to end of string.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 06 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String in which to escape RegEx special characters¶" &
"EXAMPLES: →Formula¶" &
"Formula →RXEscapeSpecCharsλ(""$D$81"")¶" &
"Result →\$D\$81#\s*",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
IsRangeRef, IsRangeRefλ( String),
Escaped, LET(Count, LEN( String),
Counter, SEQUENCE( Count),
REDUCE( "", Counter,
LAMBDA( Acc, n,
LET(
Char, MID(String, n, 1),
IF( RegExTest( Char, RegExChars, 1),
Acc & "\" & Char,
Acc & Char
)
)
)
)
),
Result, Escaped & "\s*",
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: Formula2RXλ
DESCRIPTION:*//**Convert a formula into a regular expression pattern*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
Formula2RXλ = LAMBDA(
// Parameter Declarations
[Formula],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Formula2RXλ( Formula)¶" &
"DESCRIPTION: →Convert a formula into a regular expression pattern¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 06 2024¶" &
"PARAMETERS: →¶" &
"Formula →(Required) Formula to convert to a regular expression pattern¶" &
"EXAMPLES: →Formula¶" &
"Formula →=Formula2RXλ( ""=SEQUENCE( , 10)""),¶" &
"Result →^=\s*SEQUENCE\(\s*\,\s*10\s*\)\s*",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Formula),
// Procedure
RXArray, MAP(FormulaSplitλ(Formula), RXEscapeSpecCharsλ),
Result, CONCAT(RXArray),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: RX2Formulaλ
DESCRIPTION:*//**Revert a RegEx version of a formula to a normal formula*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
RX2Formulaλ = LAMBDA(
// Parameter Declarations
[RegExString],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RX2Formulaλ( RegExString)¶" &
"DESCRIPTION: →Revert a RegEx version of a formula to a normal formula¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 06 2024¶" &
"PARAMETERS: →¶" &
"RegExString →(Required) RegEx string to revert back to its formula¶" &
"EXAMPLES: →Formula¶" &
"Formula →RX2Formulaλ(""^=\s*SCAN\(\s*0\s*\,\s*$D$81#\s*\,\s*SUM\s*\)\s*"")¶" &
"Result →=SCAN(0,$D$81#,SUM)",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( RegExString),
// Procedure
ReplaceSpace, RegExReplace( RegExString, "\\s\*", " "),
Result, RegExReplace( ReplaceSpace, "\^|\\|\.\*", ""),
// Return Result
IF( Help?, Help, Result)
)
);
// "Is" Functions
/* FUNCTION NAME: IsArrayλ
DESCRIPTION:*//**Determine if a string is enclosed in curly brackets*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsArrayλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsArrayλ( String )¶" &
"DESCRIPTION: →Determines if a string is enclosed in curly brackets¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsArrayλ({1,2,3})",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & Array & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsBracketedλ
DESCRIPTION:*//**Determine if a string is enclosed in square brackets*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsBracketedλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsBracketedλ( String )¶" &
"DESCRIPTION: →Determines if a string is enclosed in square brackets¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsBracketedλ([This is Bracketed])",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^\[.*\]$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsCellRefλ
DESCRIPTION:*//**Determine if a string is a Range reference*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsCellRefλ = LAMBDA(String,
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsCellRefλ( String )¶" &
"DESCRIPTION: →Determines if a string is a single cell Reference¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsCellRefλ(""D10"")",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & SingleCellRef & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsCommentλ
DESCRIPTION:*//**Determine if a string is a LAMBDA comment*/
/* REVISIONS: Date Developer Description
Dec 18 2024 Craig Hatmaker Copyright
*/
IsCommentλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsCommentλ( String )¶" &
"DESCRIPTION: →Determines if a string is a LAMBDA comment¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsCommentλ(""//Test"")",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & LAMBDAComment & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsFunctionλ
DESCRIPTION:*//**Determine if a string is a function*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsFunctionλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsFunctionλ( String )¶" &
"DESCRIPTION: →Determines if a string begins with a function¶" &
"→Note! A function MUST have parentheses. Example: SUM().¶" &
"→However, we are not concerned about arguments so we only¶" &
"→check up to and including the open parenthesis.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsFunctionλ(""STDEV.P()"")",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Name, "^" & TEXTBEFORE( String, "(") & "(/s*",
Result, IF( ISNA( Name),
FALSE,
REGEXTEST( Name, Function, 1)
),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsQuotedλ
DESCRIPTION:*//**Determine if a string is enclosed in double quotes*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsQuotedλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsQuotedλ( String )¶" &
"DESCRIPTION: →Determine if a string is enclosed in double quotes¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsQuotedλ(""This is quoted"")",
"→", "¶"
)),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & QuotedString & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsRangeRefλ
DESCRIPTION:*//**Determine if a string is a Range reference*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsRangeRefλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsRangeRefλ( String )¶" &
"DESCRIPTION: →Determines if a string is a cell/range Reference¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsRangeRefλ(""$D1#:D10"")",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & RangeRef & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: IsStrucRefλ
DESCRIPTION:*//**Determine if a string is a structured reference*/
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
IsStrucRefλ = LAMBDA(
[String],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IsStrucRefλ( String )¶" &
"DESCRIPTION: →Determines if a string is as structured Reference¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 14 2024¶" &
"PARAMETERS: →¶" &
"String →(Required) String to test.¶" &
"EXAMPLES: →=IsStrucRefλ(""Table1[#All]"")",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( String),
// Procedure
Result, REGEXTEST( String, "^" & StructuredRef & "$", 1),
// Return Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: AreRefsSameλ
DESCRIPTION:*//**Determine if two reference strings are the same range*/
/* REVISIONS: Date Developer Description
Feb 04 2025 Craig Hatmaker Copyright
*/
AreRefsSameλ = LAMBDA(
[Reference1],
[Reference2],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION →AreRefsSameλ( Reference1, Reference2)¶" &
"DESCRIPTION→Determine if two reference strings are the same range¶" &
"WEBPAGE →<coming soon>¶" &
"VERSION →Feb 04 2025¶" &
"PARAMETERS:→¶" &
" Reference1→(Required) First range address or name.¶" &
" Reference2→(Required) Second range address or name.¶" &
"EXAMPLES: →¶" &
" Formula →=AreRefsSameλ(""A1:B2"", ""$a$1:$B$2"")¶" &
" Result →TRUE",
"→", "¶" )),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Reference1),
ISOMITTED( Reference2)
),
// Procedure
Test, LAMBDA( First, Second,
LET(
Range1, INDIRECT(First),
Range2, INDIRECT(Second),
Anchor, CELL("address", Range1) = CELL("address", Range2),
Rows, ROWS(Range1) = ROWS(Range2),
Cols, COLUMNS(Range1) = COLUMNS(Range2),
Result, AND(Anchor, Rows, Cols),
Result
)
),
Result, IF( COUNTA( Reference1) = 1,
Test( Reference1, Reference2),
MAP( Reference1, Reference2, Test)
),
// Return Result
IF( Help?, Help, Result)
)
);
// "Get" functions
/* FUNCTION NAME: GetOprsλ
DESCRIPTION:*//**Extract Excel operators from a formula string */
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
GetOprsλ = LAMBDA( Formula, RegExExtract(Formula, Operators, 1));
/* FUNCTION NAME: GetFunctionsλ
DESCRIPTION:*//**Extract Excel functions from a formula string */
/* REVISIONS: Date Developer Description
Dec 06 2024 Craig Hatmaker Copyright
*/
GetFunctionsλ = LAMBDA( Formula, RegExExtract(Formula, Function, 1));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment