excel - How to use 3 cells to actually complete the test

Question

Ok, so I did have a quick search but did not find an answer that does what I would like... I have a horrible feeling that it is simple.. :)

I would like a formula in cell D1 that will use cells A1, B1 and C1 to evaluate the test shown in B1. This is for producing questions.

The picture shows the data and results.

I have tried indirect() with no success and &, ie

=A1&B1&C1

Answer 1

You can use the Evaluate method in excel VBA. Note that i'm using .Value on the rng2. This is because we want to use the displayed value of the cell (in the format in which it appears to the user lookins at the cell) rather than the underlying value that excel stores in the cell (which is what .value2 gives us).

Public Function EEE(rng As Range, rng2 As Range, rng3 As Range)
    EEE = Evaluate(rng.Value2 & rng2.Value & rng3.Value2)
End Function

Note that there is a method of doing it using only worksheet functions, but it rather complicated compared to the VBA solution. An excellent descriptio of the old Evaluate() function and how to use it is given in this article.