Excel Formula to Text

[cameronfraser]

This is not a VBA question, but it realates to EXCEL. In EXCEL Is it possible to show a formula as text using spreadsheet functions?

For example. In cell A1 I have the Formula = 1 + A3.

In cell A5 I want to show the formula as text. I want cell A5 to be "= 1 + A3". Below is what it would look like in EXCEl:

A B C D
1 7
2
3 6
4
5= 1 + A3


Note: I know that I can set in Options to view formulas, but I don't want to view the formula. I want it as a text value so I can manipulate its' contents in other formulas.

 

[Rjc8513]

Try

Range("A5&quot.Select
ActiveCell.Value=Range("A1&quot.Formula

Richard...

 

[cameronfraser]

Thanks for the response,

The above solution will work in VBA code.

Is there a solution that will work in EXCEL as a formula.

I would like a formula in the EXCEL spreadsheet return the actual text of a formula.

 

[Store300]

Through the Tools>Options menu select the 'View' tab and tick the 'formulas' box. Store300

Store300@ftnetwork.com

 

[DuckBill]

If you just want to show the formula in a particular cell as text, you can always prefix it with a single inverted comma (') thus:

'=1+A3

Another alternative is to put the whole formula into inverted commas thus:

="=1+A3"

Both of the above will display the text

=1+A3

in the appropriate cell. It is difficult to suggest anything else without knowing how you want to use the result. What do you have in mind?

Regards,

Bill

 

[VBArt]

Cameron,
I can't find a resident function to do this, but you can easily create a user-defined function in the Class Module of your workbook:
Function FormatText(C As Range) As String
FormatText = C.Formula
End Function

and then use the function as normal:
= FormatText(A1)

I hope that helps.
VBArt