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)
Watch Question

Kyle AbrahamsSenior .Net Developer

hit ctrl+h

in the first box:

in the second box

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

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.


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

You're welcome Alex!
Rob HensonFinance Analyst

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
I found this question previously answered:


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:


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

Rob H