Link to home
Create AccountLog in
Avatar of Star Gazr1
Star Gazr1Flag for United States of America

asked on

Excel Concatenation Formula

in Excel 2019,  I am trying to apply a calculation to a concatenated column.  Attached is a sample spreadsheet. I am also attaching a screenshot as a reference to the info below. Column A and B has the values. Column C is concatenatenation of Column A and Column B Values
In Column D, I am trying to see if there is a method to reference the data in Column C and have it become a calculation.
Column E shows how to do this manually with a formula. I am working with a lot of information and the values change in column A and Column B, so that is why the way I set this up with the concatenation is going to be the easiest way to update, unless there is another method i haven't thought of.
Any info is appreciated.  Thanks very much.User generated imageConcatenate_Formula.xlsx
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

you probably need to write a macro function to evaluate it

Function ev(r As Range) As Variant
      ev = Evaluate(r.Value)
End Function

Open in new window

to apply:

=ev(C2)

Open in new window

Excel function that evaluates a string as if it were a formula?
https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula

Concatenate_Formula_b.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Alan
Alan
Flag of New Zealand image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
The old Excel 4 macro language had a function called EVALUATE which would do exactly what you want. Although that language has been functionally obsolete since Excel 5 and VBA were released in 1993, it still works.

I suggest setting up a named formula using EVALUATE. To do so:
  1. Select cell D2 in your sample workbook
  2. Open the Formulas...Name Manager menu item
  3. Create a new name for the Name field. I called mine Evaluator. Then in the Refers to field enter this formula and click OK.
=EVALUATE(C2)

  4. You may then use a worksheet formula in cell D2 and copy it down to get your answers:
=Evaluator

Note that the parameter being evaluated is passed by its relative position to the cell with the formula. By selecting cell D2 when I created the named formula and then using cell C2 in the Refers to field, Excel knows that the parameter will be coming from the cell to the left of the one with the formula =Evaluator.
One thing I forgot to mention: you must save the file as .xlsm to preserve your named formula. You must also enable macros when you open the file.
Concatenate_Formula.xlsm

Because the named formula isn't passed any parameters directly, it won't update when you change the expression in the cell to its left. You need to select the cell containing =Evaluator, then click in the formula bar and hit Enter to make it re-evaluate.

 The SuperUser link in @Ryan Chong's answer shows a nice trick for making the named formula volatile. In so doing, it recalculates every time the user changes the value in any cell. The trick is to change the Refers to formula in the Name Manager to something like:
=IF(TODAY(),EVALUATE(C2))

TODAY returns a positive number, so the IF always returns its TRUE option. And because TODAY is volatile, any formula using it will be volatile--which is exactly what we need.
Avatar of Star Gazr1

ASKER

thanks very much for the help I really appreciate it. I should have mentioned I can't use a macro. I am restricted from running macros.  I did get some help with another option using: =LEFT(C1,FIND("/",C1)-1)/(RIGHT(C1,LEN(C1)-FIND("/",C1)))
I was hoping if I there might be other methods as I have to do a lot of different calculations with a very large spreadsheet so that is why I posted here.  
It's not clear, to me at least, why you need to do it that way.

The example I posted above seems to solve the problem, but I assume not?

What about that approach does not work?

Thanks,

Alan.
If you were sitting in the cubicle next to mine, I'd challenge your statement "I am restricted from running macros." Who is doing the restricting, and why? If I got any sense that it was a pointy-haired boss who had no clue how difficult he was making your job, I'd go right into his office and explain the facts of life to him. The two good solutions to your problem are either to avoid trying to evaluate text expressions or to use code to do it.

There is no "formula only" solution to the general problem of evaluating a text expression without using either Excel 4 macros, VBA macros or Office-js macros. There may, however, be formula only solutions to problems where the text expression falls into one of a small number of different patterns.

If you want to perform the calculations without using a macro, you need to define the full extent of variability in your expressions to be calculated. When you post a workbook with sample data, don't show 26 variations on the exact same structure. Instead, show one each of all the different types of expressions that might be encountered. Among the considerations:
  1. What are the possible operators (+, -, /, *, ^)?
  2. Are there any functions (sine, log, etc.)?
  3. What is the maximum number of operators in an expression?
  4. Might the numbers in the expressions use decimal points or commas?
  5. Might parentheses or square brackets be used?

The simpler the problem definition, the more likely it is that somebody will build a formula for you. If the problem can be set up so the expression is already parsed, that would make evaluating the expression easier. Parsed means the expression is built from several cells, each of which contains either a number or an operator. Your sample problem would therefore need five cells, three for numbers and two for operators.