Forum Discussion

willwonka's avatar
willwonka
Copper Contributor
Apr 30, 2026

Extract Functions from a Formula

Hello, I often go to the Excel BI Challenges on linked in.  I have started my own spreadsheet that I use as a reference.  Anyway, what I do is take one of the formulas that has been given in the comments of the challenge and then try to break it down and learn from it.  I think the best way would be to give an example.  In challenge number 379, my contents page looks like this for this challenge:

The formula I used is given by Bo Rydobon who often gives great formulas as well as a host of others.

Underneath the formula, you can see that I manually type in all of the functions used in the formula and then I have a LAMBDA formula that takes that list and sorts it (  =LAMBDA(A,UPPER(TEXTJOIN(", ",,SORT(TEXTSPLIT(A,,", ")))))   )

 

Your mission, if you choose to accept it is to figure out a formula that will extract all the functions from the formula ideally sorting them with the assumption that all variables are lower case and only the fucntion names are in upper case.

Here is the formula again that is not a picture:

=MAP(A6:A14,LAMBDA(a,LET(n,--TEXTSPLIT(a,,","),TEXTJOIN(", ",,MAP(DROP(n,-1),SEQUENCE(ROWS(n)-1),LAMBDA(m,i,REPT(m,AND(m<DROP(n,i)))))))))

 

Thanks in advance.

 

PS Bonus Question.  This formula uses an AND function; but only gives one argument.  Is this because the array contains more than one value?  What reasons would use the AND or OR and only use one argurment.

 

Thanks again.

excel
Formulas and Functions

3 Replies

  • willwonka's avatar
    willwonka
    Copper Contributor
    May 01, 2026

    Thanks to both of you.  I have never really looked at REGEX functions as I thought they were too confusing.  After seeing these results and watching a couple of videos on REGEX, I can see that they aren't that bad.  They just have a lot of parts to them.

     

    Thanks again.

     

  • IlirU's avatar
    IlirU
    Iron Contributor
    May 01, 2026

    Hi willwonka​,

    I'm assuming your formula is in cell A2. In the empty cell, apply this formula:

    =ARRAYTOTEXT(SORT(TOCOL(REGEXEXTRACT(FORMULATEXT(A2), "[A-Z0-9.]+(?=\()", 1))))

    This formula also considers cases when functions in Excel contain a point within them, such as the CEILING.MATH function or other functions of this type.

    HTH

    IlirU

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 30, 2026

    I'm still very much a noob with REGEX so there is probably a better way than my hybrid approach...

    =TEXTJOIN(", ",1,SORT(UNIQUE(SUBSTITUTE(REGEXEXTRACT(A1,"[A-Z]+\(",1,0),"(","")),,,1))

    and for the Bonus question,

    there may be only 1arguement but the DROP(n,i) is an array and therefor m<DROP(n,i) will still be an array in most cases and hence the need for the AND