We help IT Professionals succeed at work.

Need to replace text to formulas

I have cells with 'counta(Ink Annotations!B5:b1000) and need to do a find and replace to end up with:
=counta(Ink Annotations!B5:b1000)
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer

Commented:
hit ctrl+h

in the first box:
counta

in the second box
=counta

replace all.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Press Ctrl+H to open Find and Replace Window, in Find Waht box type ' and in Replace with box type = and click on Replace All.

Author

Commented:
Thanks
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Alex!
Rob HensonFinance Analyst

Commented:
Find and Replace annoyingly does not work when replacing an apostrophe at the beginning of a cell because Excel does not recognise it as being content of the cell.

To prove it, use =LEFT(A1,1) where A1 contains the text and the result will be c.

Likewise replacing counta with =counta does not work. You end up with '=counta
Finance Analyst
Commented:
I found this question previously answered:

https://www.experts-exchange.com/questions/25194554/convert-text-formula-to-its-mathematical-result.html

This uses a User Defined Function to evaluate the contents of a cell that look like a formula/equation.

The text of the UDF is:
Public Function EvaluateEquation(ByVal Equation As String) As Double
    EvaluateEquation = Evaluate(Equation)
End Function

Open in new window

You then use it in your worksheet like a formula:

=EvaluateEquation(A1)  where A1 contains the formula text.

However, in your example, the sheet name has a space in it and the correct syntax of formulas on sheet names with spaces needs a single quote at each end of the sheet name. To do this use:

=EvaluateEquation(SUBSTITUTE(SUBSTITUTE(A1,"(","('",1),"!","'!"))

This will replace the first instance of ( with (' and all instances of ! with '!

Thanks
Rob H