Evaluate "nested-merge" function?

  • Thread starter Thread starter Mikael Lindqvist
  • Start date Start date
M

Mikael Lindqvist

Hi everyone,

I'm using a swedish version so I don't know the exact name of the function I
used, but it "merged" the values of cell references that I inputed.

A1=Left
A2=B1
A3=4

So, I write the function (translated to english):

=MERGE("=";A1;"(;";A2;";";A3;")")

Which then reads:

=Left(B1;4)

Now, the big question... is it possible to evaluate this function directly?

Alternatively I need to copy / paste value to have it evaluated, but this
feels unnecessary cumbersome.. I'm sure there's a more clever approach - I
just need someone to point it out to me :>

Kindly,
Mikael
Sweden
 
I think the function you are referring to is CONCATENATE.

To evaluate the composite string you have built up as if it were a
formula, you could use this UDF:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then assuming that your formula is in A4, put this in A5:

=Eval(A4)

Hope this helps.

Pete
 
Before this answer I never thought about UDF, so this opens up a new range of
possibilities and for that I'm very grateful to you! :>

However, in this particular case the supplied UDF returns a standard "value
error".

(The function I supplied elow was a simplified example of a somewhat more
complicated function, but I assume the princiiple is the same?).

Anyhow, if I copy and paste the function (special -> value) it evaluates
(after I focus and enter the cell). So the function I managed to retrieve
using a "hairy" concatenation is correct.

I also tried to name the range that it evaluates to "Rng", if that has
something to do it with it (I noticed the "Rng As Range..." in your code)??

Kindly,
Mikael
 
However, in this particular case the supplied UDF returns a standard "value
error".

(The function I supplied elow was a simplified example of a somewhat more
complicated function, but I assume the princiiple is the same?).
Click to expand...

Perhaps there is a mistake in your complex formula that is causing the
VALUE error. It's always easier to help on the actual formula, so you
should post it if possible.

I might point out that you have an extra ; in the short formula above,
which causes a VALUE error. I don't know if that is just a typo here
or back in Excel, too.

Look close, this is different in the 3rd argument:
=MERGE("=";A1;"(";A2;";";A3;")")
 
Back
Top