Tom’s Tutorials For Excel: Returning a Formula’s Arguments

by Tom Urtis 5 Comments ↓

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

‹ Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges

Tom’s Tutorials For Excel: Monitoring Changes in Data With Conditional Formatting ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, User-Defined Functions

5 comments on “Tom’s Tutorials For Excel: Returning a Formula’s Arguments”

1. Neale Blackwood says:

   October 15, 2018 at 5:03 pm

   This works in Excel2013 and later versions
   =MID(FORMULATEXT(E2),SEARCH(“(“,FORMULATEXT(E2))+1,SEARCH(“)”,FORMULATEXT(E2))-SEARCH(“(“,FORMULATEXT(E2))-1)

   Reply
   • Tom Urtis says:

     October 16, 2018 at 10:01 am

     That’s interesting and useful, hadn’t seen that before. Thanks Neale!

     Reply
2. Yasser Khalil says:

   March 17, 2020 at 9:57 am

   Thanks a lot. I think using Split is easier
   Arguments = Split(Split(strFormula, “(“)(1), “)”)(0)

   Reply
3. Yasser Khalil says:

   March 17, 2020 at 9:58 am

   So this may be condensed like that

   Function Arguments(rng As Range) As String
   Arguments = Split(Split(rng.Formula, “(“)(1), “)”)(0)
   End Function

   Reply
   • Tom Urtis says:

     March 17, 2020 at 10:57 am

     That is very nice, Yasser, thank you. I’ve used Split before for other tasks but not for that. Good tip.

     Reply