You can only do it with VBA. Put the following code in a standard Code
Module in VBA.
Function EvalConcatCells(DataCells As Range) As Variant
Dim R As Range
Dim V As Variant
Dim S As String
For Each R In DataCells.Cells
S = S & R.Text & " "
Next R
On Error Resume Next
V = Evaluate(S)
If Err.Number = 0 Then
EvalConcatCells = V
Else
EvalConcatCells = CVErr(xlErrValue)
End If
End Function
You could then call this with a worksheet function like
=EvalConcatCells(A1:A4)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Yvette" wrote in message
...
Hi,
I hope I've selected the right discussion group. I want to concatenate
the
values of a set of cells as a string, and then have Excel recognise the
string as a formula. For example:
A1: 4
A2: +
A3: 4
A5: =
A6: =A1&A2&A3
I want cell A6 to return the answer to 4 + 4, and not the string "4+4".
Any ideas? (I'm using Excel 2003)
Thanks
Yvette