General discussion

Locked

Viewing Excel formulas as text or labels

By Heavy Civil ·
When entering numbers in a cell, Excel does not let you format a single cell to view the cell contents without that cell being active. All you see is the result.

I have a a worksheet with 50 rows and in cell "A1" through "A50", I sometimes apply factors to a number.
Example: Cell A1 =50*1.1
Cell A2 =8*2*8/36

In cell "B1" I would like to have this formula come over as text so when I print the worksheet, I can check to see if I have applied the right factors.
Example Output Cell B1 50*1.1
Cell B2 8*2*8/36

Is there an Excel function that lets me do this.

I am using Excel 2000

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Viewing Excel formulas as text or labels

by Fossil In reply to Viewing Excel formulas as ...

I haven't found a way, but here's teh workaround I use. You could make it into a macro, if you find it too annoying to do the steps manually. Hope it helps.
- copy column A to Column B
- highlight column B
- use EDIT:REPLACE to change all the equal signs to single quotes.
Column B now has the character representation of the exact contents of Column A

Collapse -

Viewing Excel formulas as text or labels

by Heavy Civil In reply to Viewing Excel formulas as ...

Poster rated this answer

Collapse -

Viewing Excel formulas as text or labels

by DKlippert In reply to Viewing Excel formulas as ...

Here's a simple Function posted by John Walkenbach:

You need to create a very simple custom worksheet function using VBA. The steps below assume Excel 97 or later.

1. Press Alt+F11 to activate the VB Editor
2. Select your project in the Project window
3. Choose Insert Module to insert a new VBA module
4. Enter this function into the VBA module:

Function CellFormula(c) as String
CellFormula = c.Formula
End Function

Re-activate Excel by pressing Alt+F11 again. You can then use this new
function in a formula. Here's an example that displays the formula in cell
A1:

=CellFormula(A1)

If A1 does not have a formula, the function returns the cell's value. If
you'd like the function to display an empty string if the cell does not have
a formula, use this code:

Function CellFormula(c) As String
If c.HasFormula Then
CellFormula = c.Formula
Else
CellFormula = ""
End If
End Function

Posted by John Walkenbach of JWalk & Associates
Visit "The Spreadsheet Page"
http://www.j-walk.com/ss -----

Collapse -

Viewing Excel formulas as text or labels

by Heavy Civil In reply to Viewing Excel formulas as ...

Poster rated this answer

Collapse -

Viewing Excel formulas as text or labels

by Heavy Civil In reply to Viewing Excel formulas as ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums