This file contains 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
/* | |
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)); |
This file contains 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
/* | |
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( |
This file contains 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
/* | |
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) | |
*/ |
This file contains 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
/* | |
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", |
This file contains 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
/* | |
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( |
This file contains 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
'--------------------------------------------< 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 |
This file contains 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
/* | |
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", |
This file contains 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
/* | |
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( |
This file contains 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
/* | |
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( |
This file contains 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
/* | |
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", |
NewerOlder