 # Calculating value from math in different cell

I am relatively new to this, and I apologize that I could not find a relevant topic where this was already covered.

I have a column A1 that has a value like this 5 * 3 * 14. I want to have column B1 be the mathematical product of that statement so, in this case, B1 would be 210. I tried a few things like making B1 =(A1) but that was not a solution.

That’s not at all clera to me. Is what you call the value of A1 probably the string “5 * 3 * 14”? In different word: Is the content of A1 a text?
If so: There isn’t a function in Calc fdr the evaluation of expressions given as strings.
You get your result entering the FORMULA `=5*3*14` into a cell.

Thank you for your response. Yes, the value of A1 is the text “5 * 3 *14”. I noticed that if you put the text “=5 * 3 *14” into a cell, it is automatically evaluated. I am wondering how to make one cell evaluate the text in another cell in the same manner.

If you type `=5*3*14` editing a cell, and then quit editing by `Enter`, the cell does neither get nor subsequently hold a text. The `Enter` triggers a process parsing the typed sequence of characters making an internal representation of it that can be and is evaluated. The cell is then “informed” of the result, and displays it in the chosen format. The actual content of the cell is the formula, but this formula is only shown to the user if a special option is activated, or in the formula bar if the cell is selected. If the cell is opened for editing you also get the formula again. All this is well established spreadsheet behaviour for about 4 decades now.

So I am getting the impression that what I want to do is impossible. Is my only option to make three different columns A1 = “5”, B1 = “3”, and C1 = “14” and make D1 = “=SUMPRODUCT(A1,B1,C1)”? Or is there a split function I can use on the “5`*3*`14” (splitting on “*”) that could be fed to the SUMPRODUCT function as an array?

There isn’t (yet?) a `TEXTSPLIT()` function in Calc, and if there were one, the parts returned by it would be strings (in this case consisting of decimal digits), but not nuimbers welll suitable for calculations.
Honestly speaking I think you should first collect some mexperience with spreadsheet using them how everybody does. Later you m,ay work on more ticklish things.

Maybe in B1 you could enter `=5*3*14`; and in A1, `=FORMULA(B1)`. But this will show the `=` sign in A1.

If all your “formulas” just lack a `=` equal sign at the beginning but otherwise would be valid formulas (like `5*3*14` would be (note that here you need to enquote inline expressions in ` single backticks otherwise a pair of asterisks is taken as a formatting for italics like in your previous comment, see also This is the guide - How to use the Ask site? - #6 by erAck )) then you can transform them into formulas:

1. select the cell range, e.g. data in column A
2. copy to clipboard
3. on B1 paste
pasted range should still be selected
4. invoke Edit → Find & Replace (Ctrl+H)
Find: `.+`
Replace: `=\$0`
Options:
enable Current selection only
enable Regular expressions
hit Replace All

Window with replacement results pops up, close that and dialog. Done.

2 Likes