• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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)
Alex Campbell
Alex Campbell
1 Solution
Kyle AbrahamsSenior .Net DeveloperCommented:
hit ctrl+h

in the first box:

in the second box

replace all.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
Alex CampbellAuthor Commented:
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Alex!
Rob HensonFinance AnalystCommented:
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
Rob HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now