You can

**simply copy the function codes from the text areas on this page into the Refers To section**.

Developing a LAMBDA function can be done directly in a worksheet, and you can test if it works, before you enter it under the Name Manager.

Please see Microsoft support page about LAMBDA function for a full explanation.

Please see Microsoft support page about LAMBDA function for a full explanation.

*Dependent on your Excel setting you need to use semicolon or colon in formulas. Choose your setting.*

Concatenates all cells in a range. It has arguments Inputrange, Separator, optional By_columns (if False/empty then it concatenates row by row, else it concatenates column by column), optional Iterator (used for recursion - just leave it empty).

UDFConcatenateXis more robust

LAMBDA functionL_ConcatenateXis an iterative function, that calls itself. Currently, LAMBDA functions can only do a limited amount of iterations, so it can only process a limited input range (1000-2000 cells), which may be enough in your case. The UDF does not have such a limitation.

Gets the n^{th}word from an inputstring. It has arguments Inputstring, Separator, n, optional Backwards (if TRUE, then word counting starts from the end of string, else from the start).

Gets all or n words from an inputstring, starting at word m. It has arguments Inputstring, Separator, optional StartFrom (= m), optional NumWords (= n), optional Backwards (if TRUE, then the counting of m starts from the end of the string).

If the string contains less than n words counting from the m^{th}word, then the function will only return all words available from m till the end.

Finds a value with cells. It has as arguments Searchvalue, Searchrange, MatchLength (if only exact match qua length are accepted). It is abasic version compared to the more evolved UDF.

Based on L_RangeToList, so you need to copy in that function as well.

Converts a 2-dimensional range into a 1-dimensional list. It has argument Inputrange, and optional By_columns (if true then it works column by column, otherwise row by row which is the default).

Swaps rows, columns, or both rows and columns of a range. It has arguments Inputrange, optional ReverseRows (true/false), optional ReverseColumns (true/false).

Calculates the as-the-crow-flies-distance, the shortest distance between two geographical locations specified by latitude and longitude. It has arguments FromLatitude, FromLongitude, ToLatitude, ToLongitude, InMiles (optional, if TRUE, then distance in miles is returned, if omitted/FALSE, then in kilometers = default.)

Seee also separate webpageas-the-crow-flies distance function. The demo file contains three alternatives: regular Excel formula, UDF (VBA), and LAMBDA function.

Interpolates within a table, on one dimension X or Y. It has arguments SearchValue, SearchRange, ValueRange.

See also separate pageExcel - Linear Interpolationfor a full explanation.

Interpolates within a table, on two dimensions X and Y. It has arguments searchX, searchY, rangeX, valuerange (top row only!).

It usesL_InterpolateXso copy that function in as well.

See also separate pageExcel - Bilinear Interpolationfor a full explanation.