Using the text of a formula

I was asked an interesting question by a follower called Frederick recently. He had a column that contained the syntax behind some formulae, but the column of information was stored as text. All of his entries were of the form A*B. So the column might have read:

4*9
3*7
22*13
13*15

None of the formulae had a leading equals sign, and so they rendered as they appear above.

Frederick wanted a second column that contained the answer. So in the above example:

36
21
286
195

I was searching for a way of simply referencing the formulae in a different cell. I hoped INDIRECT might work, but alas not. So instead I used the following

=VALUE(LEFT(A1,FIND("*",A1,1)-1))*VALUE(MID(A1,FIND("*",A1,1)+1,100))

It's a bit clunky, but it does the job given the consistency of the formulae. It takes the value of the stuff to the left of the asterisk, then multiplies this by the stuff to the right.

• Tweet