[SOLVED] Extracting formula's argument using another formula

Extracting formula's argument using another formula

I need to be able to extract a specific hard-coded argument value from a large number of cells into a new cell.

Let's say a cell has the following formula:

=func("value1","value2","value3")

I want to extract value2 into its own cell, all values are of variable length.

I've been able to retrieve the formula as text by adding the following to my name manager:

=GET.CELL(6,INDIRECT("RC[-2]",FALSE))

But this doesn't really help as the values being variable mean the string length is highly variable on the function values.

Solution

I can't find any official information on GET.CELL, so I assume it is a user defined function.

This means vba is already being used. As such, I purpose a new UDF:

Function FuncArg(ByVal r As Range, ByVal n As Long)
    Dim a As String
    a = Split(r.Resize(1, 1).Formula, "(", 2)(1)
    a = Split(a, ")")(0)
    a = Split(a, ",")(n - 1)
    FuncArg = Application.Evaluate(a)
End Function

So in Excel you could just use formula =FuncArg(A1, 2) to return second argument of function in cell A1.

Caution, this function must be used under very controlled situations:

using Application.Evaluate makes it quite unsafe;
only checks first function;
no parsing for nested functions or string encoded , or ).