How to Copy a Formula and Paste it as Text in Excel - 2 Methods

Method 1 – Use the Text Import Wizard and the Notepad to Copy a Formula and Paste it as Text in Excel

1.1 Display Cell Formulas

The image below shows the formula result in column E.

Create a new column with a column header: Formula.
Enter the following formula in F5.

= FORMULATEXT(E5)

It will display the hidden formula in E5.

Press ENTER.

The hidden formula used in E5 is displayed in F5.

Drag the Fill Handle icon to the end of the column.

All formulas used in column E are displayed:

1.2 Use the Notepad

Select all formulas in column F.
Press CTRL + C to copy them.

Go back to the Windows desktop and right-click it.
Choose New.
Follow the arrow direction and select Text Document.

This will open the Notepad.

Press CTRL + V to paste the copied formulas into the Notepad.
Select all the formulas and press CTRL + C.

1.3 Use the Text Import Wizard

Create a new column to paste the formulas as text. Here, Formula as Text in column H.
Select the top cell in the new column.
Go to Home > Paste > Use Text Import Wizard.

In the dialog box, check Delimited and click Next.

Uncheck all options in Delimiters.
Click Next.

In Column Data Format, select Text and click Finish.

The formulas were pasted as text in Formula as Text, column H.

Method 2 – Using a VBA Code to Copy a Formula and Paste It as Text in Excel

Press ALT + F11 to open the VBA editor.
Create a new Module in Insert.

Enter the following VBA code.

Sub PasteFormulaAsText()

  Dim aa As Long

  Dim bb As Long

  Dim cc As String

  Dim dd As String

  aa = Cells.Find(What:="*", SearchOrder:=xlByColumns, _

                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1

  bb = Cells(Rows.Count, "E").End(xlUp).Row

  cc = Columns("E").SpecialCells(xlCellTypeFormulas)(1).Formula

  dd = Left(cc, 3)

  With Cells(1, aa).Resize(bb)

    .Value = Range("E1:E" & bb).Formula

    .SpecialCells(xlCellTypeConstants).Clear

    .Replace dd, Mid(dd, 2), xlPart

  End With

End Sub