Microsoft
Thank you for the details and your innovative solution, although frankly, I have yet to catch up with these new functions and more particularly their interface, interactions and nesting which you have displayed above, quite beautifully. I am just a starter yet.
Just to elaborate in brief details, my use case is little different. I have excel add-in xlwings which plugs python UDF to excel - That add-in is not a recent addition to excel but has been around since long (As I see, you are a veteran and hence must be knowing about it). We can use python UDF out-of-the-box in excel with that add-in. I am not going here further into it's other equally useful functionality - writing SQL queries within excel. That would be out of context here.
I had a use case requiring regular expressions in excel which till date are not supported (except with vb.net). I am doing it through this add-in because python has a comprehensive support for that. My custom UDF nested within excel FILTER function is like this:
=FILTER(FILTER(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))"),(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))")<>"")*(REGEXFINDM(A2:A669,"(?i)(Open Ended)")<>"")<>1),FILTER(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))"),(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))")<>"")*(REGEXFINDM(A2:A669,"(?i)(Open Ended)")<>"")<>1)<>"")
Since the above formula itself is little overwhelming for me and took time to develop it to work (REGEXMFIND is my custom python UDF called from excel), nesting it further with LET, DROP, VSTACK, Padding, etc. would have made it very difficult to handle.
Since, array of arrays which excel does not cope with out-of-the-box (as you pointed out rightly), rather than using above complicated maze of nested functions, I simply defined another UDF in python (below) - very simple two liner and wrapped my above Regular expression UDF within that, which worked flawlessly. Python treats excel range as an array (not array of arrays) and output is array of arrays which is a pre-requisite for xlwings add-in to work with excel.
Python script:
import xlwings as xw
@xw.func
@xw.arg('data', ndim=1)
def SPLIT_TEXT(data, delimiter):
return [cell.split(delimiter) for cell in data]
Using above I can do it with only one wrapper rather than dealing with all above complications natively within excel:
=SPLIT_TEXT(FILTER(FILTER(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))"),(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))")<>"")*(REGEXFINDM(A2:A669,"(?i)(Open Ended)")<>"")<>1),FILTER(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))"),(REGEXFINDM(A2:A669,"((?i)(debt)|(?i)(liquid)|(?i)(short duration))")<>"")*(REGEXFINDM(A2:A669,"(?i)(Open Ended)")<>"")<>1)<>""),";")
I know my solution is not very elegant, but it is relatively simpler: a single python UDF wrapper to my already complicated formula with nested functions.
PS: I have mentioned above DROP, VSTACK, etc. which have not been covered in your custom solution, but I got that other workaround (involving these functions) from stackoverflow link which I have mentioned above in one of my postings.
