Need to replace text to formulas

Alex Campbell
Alex Campbell used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial