The Lambda function and its reliance on the Excel Name Manager

George_Rickerson I find the lambda function useless in name manager. Even if I try to use it in another different cell in the same worksheet where I defined it, it doesn't work the same way. Using it on a different sheet or different place in the same sheet is impossible. Here is an example I named splitString:

=LAMBDA(string,delimiter,TRIM(MID(SUBSTITUTE(string,delimiter,REPT(" ",LEN(string))),(COLUMNS($B:B)-1)*LEN(string)+1,LEN(string)))

Note the column array $B:B. Reference the string "aa bb cc" and add delimiter " " and copy across 3 columns to populate 3 cells with aa in first cell, bb in second cell, and cc in third cell. If you use column C to start then $B:B becomes say $B:C and it fails to populate properly. The problem is that $B:B changes depending on the column you start the function.

If you use this instead without any cell references in the formula:

=LAMBDA(colArray,string,delimiter,TRIM(MID(SUBSTITUTE(string,delimiter,REPT(" ",LEN(string))),(COLUMNS(colArray)-1)*LEN(string)+1,LEN(string)))

then use it like this 

=splitString($B:B,string,delimiter)

you can use it anywhere as long as you use $B:B or  any adjacent columns such as $F:F etc. COLUMNS(colArray) is used as a counter. This can be used on any spreadsheet in the workbook adjusting the column array and string reference appropriately.