Solved

Need to replace text to formulas

Posted on 2016-09-15
6
51 Views
Last Modified: 2016-09-16
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)
0
Comment
Question by:Alex972
6 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 41800595
hit ctrl+h

in the first box:
counta

in the second box
=counta

replace all.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41800597
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.
0
 

Author Comment

by:Alex972
ID: 41800615
Thanks
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41800620
You're welcome Alex!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41801155
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
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41801202
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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 6 19
Changing absolute cell references 3 22
TT Master To Fresh - Rework 4 21
Response to Macro Filter Fix 2 7
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now