REPLACEBLOCK

=REPLACEBLOCK(array, i, j, replace_with)

Download the Template

In the template file, navigate to the Arrays worksheet to see the REPLACEBLOCK function in action.

Description

REPLACEBLOCK fills a niche (pun intended) where you need to replace either a single value or an entire block of elements within an array with a new value or array. When all other methods of modifying the array or matrix fall short, REPLACEBLOCK can get the job done.

Some matrix algorithms work with blocks, but Excel does not have an assignment operator such as A(i,j)=5 for easily replacing specific values in an array by location. You can use DROP, TAKE, CHOOSEROWS, CHOOSECOLS, and INDEX to retrieve a block from an existing array. But, if you want to replace a block based on the (i,j) location, it may be more tricky to use HSTACK, VSTACK to reassemble a new matrix. This is where REPLACEBLOCK may be handy.

Lambda Formula

This code for using REPLACEBLOCK 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.

/**
* Replace a block within an array by specifying the starting (i,j) location
*/
REPLACEBLOCK = LAMBDA(array,i,j,new_block,
LET(doc,"https://www.vertex42.com/lambda/replaceblock.html",
    mrows,ROWS(new_block),
    ncols,COLUMNS(new_block),
    MAKEARRAY(ROWS(array),COLUMNS(array),
        LAMBDA(r,c,
            IF(AND((r-i+1)>0,(r-i)<mrows,(c-j+1)>0,(c-j)<ncols),
                INDEX(new_block,r-i+1,c-j+1),
                INDEX(array,r,c)
            )
        )
    )
));

Name: REPLACEBLOCK
Description: Replace a block within an array by specifying the starting (i,j) location
Arguments: array, i, j, new_block
Function:

=LET(doc,"https://www.vertex42.com/lambda/replaceblock.html",
    mrows,ROWS(new_block),
    ncols,COLUMNS(new_block),
    MAKEARRAY(ROWS(array),COLUMNS(array),
        LAMBDA(r,c,
            IF(AND((r-i+1)>0,(r-i)<mrows,(c-j+1)>0,(c-j)<ncols),
                INDEX(new_block,r-i+1,c-j+1),
                INDEX(array,r,c)
            )
        )
    )
)

REPLACEBLOCK Examples

Test: Copy and Paste this LET function into a cell
=LET(
    array, PASCAL(4),
    i, 1,
    j, 1,
    REPLACEBLOCK(array,i,j,MUNIT(2))
)

Result:
{1,  0,  1,  1;
 0,  1,  3,  4;
 1,  3,  6, 10;
 1,  4, 10, 20}

Test: Copy and Paste this LET function into a cell
=LET(
    array, {"🪨","🩸","🌡️";"🧱","🔋","🩸";"🔩","🧱","🔋";"🪨","🧲","🧱"},
    i, 3,
    j, 2,
    REPLACEBLOCK(array,i,j,"")
)

Result:
{"🪨","🩸","🌡️";
 "🧱","🔋","🩸";
 "🔩", "" ,"🔋";
 "🪨","🧲","🧱"}