Excel Formula Situation; Relatively Direct Method to Produce Formula Counterparts for Text Strings

[PivotResults]

I had an Excel file, perforce, where I wanted to identify the cells containing formulas. I recalled the ISFORMULA function. I used the ISFORMULA function in tandem with an IF function and FORMULATEXT to reproduce the formulas in the respective cells.

The complication occurs in that the formulas only populate as text, and to implement the text strings as formulas seems to entail clicking within the cells to render the text strings as formulas. While I had only a feasible number of cells to handle in this situation, to make this situation scalable, I inquire on a method, barring visual basic, to produce formula counterparts of text strings. CLEAN, TRIM, LEN and LEFT seem less helpful.

Visual Basic and Macros stand as barred for solutions.

 

[JackDanIce]

Difficult to understand what you're asking without context and over descriptive text, without actual detail, specifically:

- What is your current formula?

- Why use FORMULATEXT to reproduce existing formulae text before manually editing the text string results as formulas?

- What is the edit you do?

- Why can't you enter the formula directly?

In other words, what are you trying to do, bearing in mind only you can see your screen. Sheet name? Cell address? More precise details and less descriptive text may help!

 

[PivotResults]

I had an Excel file. I wanted to only reproduce the formulas from the file/sheet, rather than inadvertently reproduce data from the cell, so as to reproduce said formulas in another file/sheet. I determined an approach to this situation entailed using an IF function in another cell/perhaps sheet, to evaluate, as an example, cell L15 with the IF function evaluating a cell as containing a formula using ISFORMULA. If the cell contents served as other than a formula, standing as "FALSE", then the formula would produce a "-". If the cell contained a formula, standing as "TRUE", then I used the FORMULATEXT function as embedded in the IF function in the "TRUE" area to reproduce the formula. As a hypothetical example, (Q2/S4)+(D3^N9).

I copied and pasted special to only reproduce values to another sheet in another file. The formula (Q2/S4)+(D3^N9) populated as anticipated. Unhelpfully though perforce perhaps anticipated, such populated as a text string, so to calibrate that to a formula, I placed the cursor in the cell and such did render a formula in the cell. To do this maneuver multiple times seems rather less than scalable, so I inquired on an alternative.

 

[Fluff]

Why not just copy/paste the formulas?
Ctrl G > Special > Formulas > OK > Ctrl C
Then goto to the new sheet & paste. Just make sure that you paste in the correct location.

 

[PivotResults]

"Why not just copy/paste the formulas?
Ctrl G > Special > Formulas > OK > Ctrl C
Then goto to the new sheet & paste. Just make sure that you paste in the correct location."

I wanted a reliable, scalable way to assure that cells featuring only values, that only contents of cells with formulas stand as covered, that items with values stand as excluded, to prevent data from one cell proceeding to another sheet. Will only formulas ensue from the method suggested?

 

[Fluff]

Have you tried it?
If so does it do what you want?