Work in Progress :: Please contact us to report errors, typos, etc.
L_SPLICE
Delete rows or columns and insert an array within another array
=L_SPLICE(array, start_index, delete_count, [insert_array], [by_col])
| Argument | Description | Example |
|---|---|---|
| array | The original array to modify | |
| start_index | The index location within the original array to begin deletion and insertion | 2 |
| delete_count | [optional, default=0] The number of rows (or columns) to delete, starting with the start_index | 1 |
| insert_array | [optional] An array to insert before the start_index | {1;2;3} |
| by_col | [optional, default=FALSE] Delete and insert by column instead of by row | FALSE |
Description
A splice in real life can mean cutting a rope or wire and attaching another segment within the cut area, either to repair or extend it. That is pretty much the same thing that L_SPLICE does with an array. It's like deleting and inserting rows and columns, except you are doing it with a function rather than manually, and the result is a copy of the original array with the changes applied.
L_SPLICE was modeled after the JavaScript splice function which does nearly the same thing, though with some differences.
Lambda Formula
This code for using L_SPLICE in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Splice an array to delete or insert rows or columns */ L_SPLICE = LAMBDA(array,start_index,delete_count,[insert_array],[by_col], LET(doc,"https://www.vertex42.com/lambda/splice.html", start,IF(start_index<1,1,start_index), by_col,IF(ISOMITTED(by_col),FALSE,by_col), no_insert,ISOMITTED(insert_array), no_first,start<=1, first,IF(by_col,TAKE(array,,start-1),TAKE(array,start-1,)), no_last,delete_count>IF(by_col,COLUMNS(array),ROWS(array))-start, last,IF(by_col,DROP(array,,start+delete_count-1),DROP(array,start+delete_count-1,)), tf,CONCATENATE( IF(no_first,"T","F"), IF(no_insert,"T","F"), IF(no_last,"T","F"), IF(by_col,"T","F") ), SWITCH(tf, "TTTT",NA(),"TTTF",NA(), "TTFT",last,"TTFF",last, "TFTT",insert_array,"TFTF",insert_array, "TFFT",HSTACK(insert_array,last), "TFFF",VSTACK(insert_array,last), "FTTT",first,"FTTF",first, "FTFT",HSTACK(first,last), "FTFF",VSTACK(first,last), "FFTT",HSTACK(first,insert_array), "FFTF",VSTACK(first,insert_array), "FFFT",HSTACK(first,insert_array,last), "FFFF",VSTACK(first,insert_array,last), ) ));Note: The complexity of the function comes from the fact that HSTACK and VSTACK cannot have NULL or empty values as parameters. If the first, insert, and last arrays could be allowed to be empty, then VSTACK(first,insert,last) would work without needing the SWITCH logic. This is a failing of Excel to define a truly null value with Excel.
Named Function for Google Sheets
Name: L_SPLICE Description: Delete rows or columns and insert an array within another array Arguments: array, start_index, delete_count, insert_array, by_col Function: =LET(doc,"https://www.vertex42.com/lambda/slice.html", start,IF(start_index<1,1,start_index), by_col,IF(ISBLANK(by_col),FALSE,by_col), no_insert,AND(ROWS(insert_array)=1,COLUMNS(insert_array)=1,ISBLANK(INDEX(insert_array,1,1))), no_first,start<=1, first,IF(by_col,L_TAKE(array,,start-1),L_TAKE(array,start-1,)), no_last,delete_count>IF(by_col,COLUMNS(array),ROWS(array))-start, last,IF(by_col,L_DROP(array,,start+delete_count-1),L_DROP(array,start+delete_count-1,)), tf,CONCATENATE(IF(no_first,"T","F"), IF(no_insert,"T","F"), IF(no_last,"T","F"), IF(by_col,"T","F") ), sw,ARRAYFORMULA(IF( OR(tf="TTTT",tf="TTTF"),NA(),IF( OR(tf="TTFT",tf="TTFF"),last,IF( OR(tf="TFTT",tf="TFTF"),insert_array,IF( tf="TFFT",HSTACK(insert_array,last),IF( tf="TFFF",VSTACK(insert_array,last),IF( OR(tf="FTTT",tf="FTTF"),first,IF( tf="FTFT",HSTACK(first,last),IF( tf="FTFF",VSTACK(first,last),IF( tf="FFTT",HSTACK(first,insert_array),IF( tf="FFTF",VSTACK(first,insert_array),IF( tf="FFFT",HSTACK(first,insert_array,last),IF( tf="FFFF",VSTACK(first,insert_array,last),NA()) )))))))))))), sw )Note: The GS SWITCH and IFS functions cannot output arrays. So the GS version of the function uses a nested IF formula.
Warning
These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.
L_SPLICE Examples
Example
Replace the Nth value in an array of text values with an UPPERcase version of the text value.
Test: Copy and Paste this LET function into a cell =LET( fruits,{"Apples";"Oranges";"Bananas";"Cherries";"Melons"}, index,3, L_SPLICE(fruits,index,1,UPPER(INDEX(fruits,index))) ) Result: {"Apples";"Oranges";"BANANAS";"Cherries";"Melons"}
References & Resources
Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.
