Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the formula
used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15).

Thanks

Hi
Place a single quote ' in the cells, before you paste.
That will force the entry to be text.
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the formula
used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15).

Thanks

Hi Roger

It worked if I placed the single quote ' at the beginning of the formula
after I had pasted it into another cell but not before.

Do you know if I can add the single quote ' automatically - some type of
add/replace function(??) at the beginning of the formula?

Regards, Silena

"Roger Govier" wrote:

Hi
Place a single quote ' in the cells, before you paste.
That will force the entry to be text.
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the formula
used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15).

Thanks

Rather than copy the formula you could use a UDF to show the formula.

Function ShowFormula(Cell)
Application.Volatile
ShowFormula = "No Formula"
If Cell.HasFormula Then ShowFormula = Mid(Cell.Formula, 2)
End Function

Usage is: =ShowFormula(cellref) will return SUM(B8:B15)


Gord Dibben MS Excel MVP

On Thu, 29 Nov 2007 13:11:00 -0800, Silena K-K
wrote:

Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the formula
used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15).

Thanks

Hi

Sorry about that.

The following short bit of code will create a list of the formulae in column
A, in column B in text form.
The code is set to run from row 1 to 50, change to suit your requirement.
Also, alter the column letters to suit.

Sub changeformulatotext()
Dim i As Long
For i = 1 To 50
Cells(i, "B") = "'" & Cells(i, "A").Formula
Next
End Sub


Copy the above code.
press alt+F11 to bring up the Visual Basic Editor
InsertModule
In the white pane that appears, paste the code you have copied
Alt+F11 to return to Excel
Alt+F8 to bring up macros, highlight the macro name and click Run
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Hi Roger

It worked if I placed the single quote ' at the beginning of the formula
after I had pasted it into another cell but not before.

Do you know if I can add the single quote ' automatically - some type of
add/replace function(??) at the beginning of the formula?

Regards, Silena

"Roger Govier" wrote:

Hi
Place a single quote ' in the cells, before you paste.
That will force the entry to be text.
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the
formula
used in each calculated cell eg: =SUM(B8:B15) is written as
SUM(B8:B15).

Thanks

Hi Roger

Thank you and have a wonderful Christmas/NY. Regards, Silena

"Roger Govier" wrote:

Hi

Sorry about that.

The following short bit of code will create a list of the formulae in column
A, in column B in text form.
The code is set to run from row 1 to 50, change to suit your requirement.
Also, alter the column letters to suit.

Sub changeformulatotext()
Dim i As Long
For i = 1 To 50
Cells(i, "B") = "'" & Cells(i, "A").Formula
Next
End Sub


Copy the above code.
press alt+F11 to bring up the Visual Basic Editor
InsertModule
In the white pane that appears, paste the code you have copied
Alt+F11 to return to Excel
Alt+F8 to bring up macros, highlight the macro name and click Run
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Hi Roger

It worked if I placed the single quote ' at the beginning of the formula
after I had pasted it into another cell but not before.

Do you know if I can add the single quote ' automatically - some type of
add/replace function(??) at the beginning of the formula?

Regards, Silena

"Roger Govier" wrote:

Hi
Place a single quote ' in the cells, before you paste.
That will force the entry to be text.
--

Regards
Roger Govier

"Silena K-K" wrote in message
...
Is it possible to copy a formula from a cell and paste it as text?

I am writing spreadsheet validation documents which must show the
formula
used in each calculated cell eg: =SUM(B8:B15) is written as
SUM(B8:B15).

Thanks