Skip to content

Instantly share code, notes, and snippets.

View ExcelRobot's full-sized avatar

Excel Robot ExcelRobot

View GitHub Profile
@ExcelRobot
ExcelRobot / CompatibilityVersion.lambda
Last active December 9, 2024 01:44
Excel Calculation Compatibility Version
/*
Name: Compatibility Version (CompatibilityVersion)
Description: Returns whether Excel calculation is in compatibility version 1 or 2.
Source: @ExcelRobot
*/
CompatibilityVersion =LAMBDA(IF(LEN("🤖") = 2, 1, 2));
@ExcelRobot
ExcelRobot / IndirectExpand.lambda
Last active December 8, 2024 20:25
Indirect Expand Lambda
/*
Name: Indirect Expand (IndirectExpand)
Description: Converts a range address (and optional sheet name) to a reference to those cells and the cells surrounding them by a user specified distance.
Parameters:
address - range address text
[sheet_name] - name of sheet, if not active sheet
[expand_distance] - number of cells in all directions to expand the selection (default: 0)
Source: @ExcelRobot
*/
IndirectExpand =LAMBDA(address, [sheet_name], [expand_distance], LET(
@ExcelRobot
ExcelRobot / Lag.lambda
Last active June 23, 2024 05:05
Lag By N Lambda Function
/*
Name: Lag By N (Lag)
Description: Return the array shifted back by the specified number of positions.
Parameters:
array - original array
[lag_by] - number of positions to lag by (default: 1)
[pad_with] - value to fill empty position with (default: #N/A)
[scan_by_column] - TRUE - scans by columns, FALSE - scans by rows (default)
Source: Excel Robot (@ExcelRobot)
*/
@ExcelRobot
ExcelRobot / StreetSort.lambda
Last active June 9, 2024 22:32
Street Sort Lambda
/*
Name: Sort Street Addresses (StreetSort)
Description: Sorts a list of street addresses so that the street names are together with the address number in the correct order. Also supports proper sorting of street names like 1st Street, 2nd Street, 10th Street into numerical order.
Parameters:
addresses - array of street addresses (ie: 123 Main Street)
Source: Excel Robot (@ExcelRobot)
*/
StreetSort =LAMBDA(addresses, LET(
\\LambdaName, "StreetSort",
\\CommandName, "Sort Street Addresses",
@ExcelRobot
ExcelRobot / RegExFromAI.lambda
Last active December 5, 2024 11:53
RegEx Expression From Generative AI LAMBDA
/*
Name: RegEx Expression From Generative AI (RegExFromAI)
Description: Given a natural language input, uses Excel Labs' Generative AI function to return a RegEx expression. Optionally uses Excel's new REGEXEXTRACT function to check whether the RegEx generated works on a provided example and expected value.
Parameters:
input - natural language prompt like: extract email address
example - optionally provide some example data to help train the AI
expected - optionally provide the expected value to be returned given the example data
Source: Excel Robot (@ExcelRobot)
*/
RegExFromAI =LAMBDA(input, [example], [expected], LET(
@ExcelRobot
ExcelRobot / modWorkbook.bas
Last active May 30, 2024 11:36
Backup And Save Active Workbook VBA Macro
'--------------------------------------------< OA Robot >--------------------------------------------
' Command Name: Backup And Save Active Workbook
' Description: Saves the active workbook, but also makes a backup in the Archive folder
' Macro Expression: modWorkbook.BackupAndSaveActiveWorkbook()
' Author: Excel Robot (@ExcelRobot)
' Generated: 08/05/2022 02:32 PM
'----------------------------------------------------------------------------------------------------
Sub BackupAndSaveActiveWorkbook()
Dim archivePath As String
Dim wbName As String
@ExcelRobot
ExcelRobot / NoExamples.lambda
Last active April 29, 2024 06:13
MEWC No Examples Lambda
/*
Name: No Examples (NoExamples)
Description: Given the used range of a MEWC (Microsoft Excel World Championship) Case sheet, returns just the game numbers, levels, and game data excluding any example data.
Parameters:
used_range - Reference to all used cells on Case sheet.
Source: Excel Robot (@ExcelRobot)
*/
NoExamples =LAMBDA(used_range, LET(
\\LambdaName, "NoExamples",
\\CommandName, "No Examples",
@ExcelRobot
ExcelRobot / RGBtoColorName.lambda
Created March 7, 2024 03:11
Convert RGB To Color Name Lambda
/*
Name: Convert RGB to Color Name (RGBtoColorName)
Description: Converts RGB integers to nearest color name in a table of 139 colors.
Parameters:
r - Red RGB Integer
g - Green RGB Integer
b - Blue RGB Integer
Source: Written By Excel Robot (@ExcelRobot), converted from https://gist.github.com/XiaoxiaoLi/8031146
*/
RGBtoColorName =LAMBDA(r, g, b, LET(
@ExcelRobot
ExcelRobot / TLOOKUP.lambda
Last active March 3, 2024 19:02
Two-Way Table Lookup LAMBDA Function
/*
Name: Two Way Table Lookup (TLOOKUP)
Description: Looks up values in a table by values in first column and first row. Accepts arrays of column/row values or if omitted will return all columns/rows.
Parameters:
table - range or array that includes headers in first row and column
[row_values] - value or array of values to lookup in first column; returns all rows if omitted
[column_values] - value or array of values to lookup in first row; returns all columns if omitted
Source: Excel Robot (@ExcelRobot)
*/
TLOOKUP =LAMBDA(table, [row_values], [column_values], LET(
@ExcelRobot
ExcelRobot / FormatFormula.lambda
Last active September 21, 2024 11:12
Format Formula LAMBDA Function
/*
Name: Format Formula (FormatFormula)
Description: Uses OA Robot formula formatting api to format a formula to make it easier to read.
Parameters:
CellOrFormula - Reference to cell containing a formula or a formula as text
Compact - True to remove all whitespace (default: False)
Source: @ExcelRobot, https://www.oarobot.com
*/
FormatFormula = LAMBDA(CellOrFormula, [Compact], LET(
\\LambdaName, "FormatFormula",