Excel:Get concatenated text to be recognised as formula not text?
 

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

Excel:Get concatenated text to be recognised as formula not text?
 

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

Excel:Get concatenated text to be recognised as formula not text?
 

Maybe some variation of this...........

=IF(A2="+",A1+A3,IF(A2="-",A1-A3,IF(A2="*",A1*A3,IF(A2="/",A1/A3,""))))

hth
Vaya con Dios,
Chuck, CABx3



"Yvette" wrote:

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

Excel:Get concatenated text to be recognised as formula not text?
 

I don't want to be pedantic, but VBA isn't your only option (altho I
think it's by far the best and most flexible and would the be route I
would choose if I needed to do this) you can also use a relatively
inflexible XLM4 macro function:

InsertNameDefine and in the Refers To box type:

=EVALUATE(A$1&A$2&A$3)

and give it a name like "FormulaResult"

Now, back in the worksheet, select A6 and type

=FormulaResult

which should return 8 as the result of 4+4.

This is jolly inflexible however (It only evaluates 3 cells for
example), but it is an alternative.

Richard


Chip Pearson wrote:

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

Excel:Get concatenated text to be recognised as formula not te
 

That sounds great. I've not worked with VBA yet, so it will be a new
experience to try it!

Thanks

Yvette

"Chip Pearson" wrote:

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

Excel:Get concatenated text to be recognised as formula not te
 

I think you're right that this method probably isn't ideal for my
requirements, but it will be a learning experience to try it out.

Thank you

Yvette

"RichardSchollar" wrote:

I don't want to be pedantic, but VBA isn't your only option (altho I
think it's by far the best and most flexible and would the be route I
would choose if I needed to do this) you can also use a relatively
inflexible XLM4 macro function:

InsertNameDefine and in the Refers To box type:

=EVALUATE(A$1&A$2&A$3)

and give it a name like "FormulaResult"

Now, back in the worksheet, select A6 and type

=FormulaResult

which should return 8 as the result of 4+4.

This is jolly inflexible however (It only evaluates 3 cells for
example), but it is an alternative.

Richard


Chip Pearson wrote:

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