Last active
August 18, 2025 01:34
-
-
Save CHatmaker/51abd8149e2f50c4aff5b6eae9b9045a to your computer and use it in GitHub Desktop.
BXL 5g Functions LAMBDA for Excel: Financial Model Utilities
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Apr 10 2025 Craig Hatmaker Original Development | |
| Apr 22 2025 Craig Hatmaker See Refs2Txtsλ | |
| */ | |
| Aboutλ = TRIM(TEXTSPLIT( | |
| "About: →BXL's Financial Model Utilities. Suggested module name: FMU¶" & | |
| "Version: →BXL: Apr 22 2025¶" & | |
| "Gist URL: →https://gist.github.com/CHatmaker/51abd8149e2f50c4aff5b6eae9b9045a ¶" & | |
| "Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" & | |
| "→¶" & | |
| "Function →Description¶" & | |
| "Aboutλ →Produces this table¶" & | |
| "Refs2Txtsλ →Convert single cell references in formulas to cell reference's labels.¶" & | |
| "Formula2LETλ →Convert single cell reference formulas to LET statements.", | |
| "→","¶" | |
| ) | |
| ); | |
| /* FUNCTION NAME: Refs2Txtsλ | |
| DESCRIPTION:*/ /**Convert single cell references in formulas to cell reference's labels.*/ | |
| /* REVISIONS: Date Developer Description | |
| Apr 10 2025 Craig Hatmaker Copyright | |
| Apr 19 2025 Craig Hatmaker Efficiency upgrade | |
| Apr 22 2025 Craig Hatmaker SheetRef fix | |
| */ | |
| Refs2Txtsλ = LAMBDA( | |
| [FormulaCells], | |
| [LabelColumn], | |
| // Help | |
| LET( | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →Refs2Txtλ(FormulaCell, FormulaLabels)¶" & | |
| "DESCRIPTION: →Convert single cell references in formulas to cell reference's labels.¶" & | |
| "WEBSITE: →<Comming soon>¶" & | |
| "VERSION: →BXL: Apr 22 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " FormulaCells →(Required) One or more cells containing formulas to convert¶" & | |
| " LabelColumn, →(Required) Entire Column range containing formula labels¶" & | |
| "EXAMPLE: →¶" & | |
| " Formula →=Refs2Txtsλ(J10:J16, E:E)¶" & | |
| " Result →2", | |
| "→", | |
| "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( FormulaCells), | |
| ISOMITTED( LabelColumn) | |
| ), | |
| // Procedure | |
| Ref2Text, LAMBDA( | |
| FormulaCell, | |
| LabelColumn, | |
| LET( | |
| // RegEx pattern for extracting sheet names from Excel cell references | |
| SheetRef, "('?[A-Za-z]+\S*'?!)", | |
| // RegEx pattern for extracting single cell references from Strings | |
| SingleCellRef, SheetRef & "?(\$?[A-Za-z]{1,3})(\$?[0-9]{1,7}#?)", | |
| // Get Formula | |
| FormulaText, FORMULATEXT( FormulaCell), | |
| // Get Formula's column # | |
| FormulaColumn, COLUMN(FormulaCell), | |
| // Get References | |
| Refs, REGEXEXTRACT( FormulaText, SingleCellRef, 1), | |
| Expanded, REDUCE( FormulaText, Refs, | |
| LAMBDA( Text, Ref, | |
| LET( | |
| RefCell, INDIRECT( Ref), | |
| RefCol, COLUMN( RefCell), | |
| Offset, COLUMN( LabelColumn) - RefCol, | |
| RefLbl, Offset( RefCell, , Offset) & | |
| IF( RefCol = FormulaColumn - 1, " <prior column>", ""), | |
| Result, SUBSTITUTE( Text, Ref, RefLbl ), | |
| Result | |
| ) | |
| ) | |
| ), | |
| Result, IF( ISNA( Expanded), | |
| IF( FormulaCell <> "", | |
| FormulaCell & " <Hard coded>", | |
| "" | |
| ), | |
| Expanded | |
| ), | |
| Result | |
| ) | |
| ), | |
| Result, MAP( | |
| FormulaCells, | |
| LAMBDA( | |
| FormulaCell, | |
| Ref2Text( FormulaCell, LabelColumn) | |
| ) | |
| ), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Formula2LETλ | |
| DESCRIPTION:*/ /**Convert single cell reference formulas to LET statements.*/ | |
| /* REVISIONS: Date Developer Description | |
| Apr 10 2025 Craig Hatmaker Copyright | |
| */ | |
| Formula2LETλ = LAMBDA( | |
| [FormulaCells], | |
| [FormulaLabels], | |
| // Help | |
| LET( | |
| Help, TRIM( TEXTSPLIT( | |
| "FUNCTION: →Formulas2LETλ(FormulaCell, FormulaLabels)¶" & | |
| "DESCRIPTION: →Convert single cell reference formulas to LET statements.¶" & | |
| "WEBSITE: →<Comming soon>¶" & | |
| "VERSION: →BXL: Apr 10 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " FormulaCells →(Required) One or more cells containing formulas to convert¶" & | |
| " FormulaLables →(Required) Cells containing labels for cell references¶" & | |
| "EXAMPLE: →¶" & | |
| " Formula →=Formula2LETλ(J10:J16, E5:E16)¶" & | |
| " Result →2", | |
| "→", | |
| "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( FormulaCells), | |
| ISOMITTED( FormulaLabels) | |
| ), | |
| // Procedure | |
| Formula2Text, LAMBDA( | |
| FormulaCell, | |
| FormulaLabels, | |
| LET( | |
| FormulaText, Refs2Txtsλ( FormulaCell, FormulaLabels), | |
| LabelRow, ROW( FormulaCell) - @ROW( FormulaLabels) + 1, | |
| Label, TRIM( INDEX( FormulaLabels, LabelRow)), | |
| Result, Label & ", " & RIGHT( FormulaText, LEN( FormulaText) - 1) & "," , | |
| Result | |
| ) | |
| ), | |
| Result, MAP( | |
| FormulaCells, | |
| LAMBDA( | |
| FormulaCell, | |
| Formula2Text( FormulaCell, FormulaLabels) | |
| ) | |
| ), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment