Tom’s Tutorials For Excel: Returning a Formula’s Arguments
I saw a question last year on one of the Excel newsgroups, asking for a way to return a formula’s arguments. I wondered, as you probably are, why this person wanted such a thing.
Still, I was intrigued at the question, so I answered it with the below User Defined Function that’s demonstrated by example in the picture.
The User Defined Function in pink cell E4 is
=Arguments(E1)
Here is the User Defined Function code:
Function Arguments(rng As Range) As String Dim strFormula$ strFormula = rng.Formula With WorksheetFunction Arguments = _ Mid(strFormula, .Search("(", strFormula) + 1, _ .Search(")", strFormula) - .Search("(", strFormula) - 1) End With End Function
This works in Excel2013 and later versions
=MID(FORMULATEXT(E2),SEARCH(“(“,FORMULATEXT(E2))+1,SEARCH(“)”,FORMULATEXT(E2))-SEARCH(“(“,FORMULATEXT(E2))-1)
That’s interesting and useful, hadn’t seen that before. Thanks Neale!
Thanks a lot. I think using Split is easier
Arguments = Split(Split(strFormula, “(“)(1), “)”)(0)
So this may be condensed like that
Function Arguments(rng As Range) As String
Arguments = Split(Split(rng.Formula, “(“)(1), “)”)(0)
End Function
That is very nice, Yasser, thank you. I’ve used Split before for other tasks but not for that. Good tip.