Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Conditional formatting based on text within a formula | Excel Worksheet Functions | |||
Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
Invisable text in formula bar | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions |