How to perform text string in one cell as function in another

Rohohoho

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi there, I need to make a spread sheet and show the equations in one column and the results in another. Is there a way to reference the text string in one cell and perform the funtion in another so I can quickly fill the cells rather than typing out twice?

for example.
cell a1 contains the text 9*12.5*52
cell b1 solves the equation =9*12.5*52

I feel like this is super simple, but I can't quite figure it out
Thank you!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I am not sure if there is an easier way, but one way would be to create your own User Defined Function in VBA that would evaluate the formula and return the value.

So you would just copy this VBA code into a new, blank VBA module in your workbook:
VBA Code:
Function MyCalc(rng As Range) As Variant
    Application.Volatile
    MyCalc = Evaluate("=" & rng)
End Function
Then you could use this like any other Excel function.
So, in your example, you would place the following formula in cell B1:
VBA Code:
=MyCalc(A1)
 

Rohohoho

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
thank you. I tried this, but get an error
1601583047240.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
The "Ambiguous name detected..." error usually means that you already have a function in your workbook with that name (or you copied and pasted my code two times in VBA instead of just once).
 

Rohohoho

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Must be a little rustier on VBA than I thought. I inserted a new module and copied the VBA code as supplied.
when I try and run the macro, I get another error. Might need to give myself more of a refresher. Thanks for your help
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
Did you get it working yesterday?
 

Forum statistics

Threads
1,148,191
Messages
5,745,251
Members
423,939
Latest member
Learnexcel1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top