How to Copy Formula and Paste as Text in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel has no built-in function to directly copy a formula and later paste it as text. Whatever you paste in Excel cells, if it resembles a formula syntax, Excel considers it as a formula and tries to return its value. But today in this article, you will learn 2 exclusive methods to copy a formula and paste it as text in Excel with ease.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Ways to Copy a Formula and Paste as Text in Excel

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

This is going to be a long procedure. That’s why I’m gonna break it into several sections for your convenience. So let’s get started.


1.1 Display Cell Formulas

In the following screenshot, you can see the formula result in column E.

At first, we will show the underlying formulas in that column. Later, you will copy those formulas and paste them into another column as texts.

To show the hidden formula, we will use a function called FORMULATEXT.

Now follow the steps below to show the hidden formulas using the FORMULATEXT Function.

❶ First, create a new column with a column header such as Formula.

❷ After that type the following formula in cell F5.

= FORMULATEXT(E5)

This formula will display the cell formula hidden in cell E5.

❸ Now hit the ENTER button to execute the formula.

This will show the hidden formula used in cell E5 in cell F5.

display formulas first to Copy a Formula and Paste as Text in Excel

❹ To apply the formula all over the Formula column, drag the Fill Handle icon to the end of the column, Formula.

Drag the Fill handle to display to Copy a Formula and Paste as Text in Excel

After that, you will see all the formulas used in column E as in the picture below:


1.2 Use Notepad

Now you need to copy the formulas into Notepad and then copy them back into the Excel worksheet.

To do that,

❶ Select all the formulas in column F.

❷ Then press CTRL + C to copy them all.

Copy a Formula and Paste as Text in Excel

❸ After that go back to the windows desktop and right-click on it.

❹ From the pop-up menu, choose New.

❺ Then follow the arrow direction and select Text Document.

Open notepad to Copy a Formula and Paste as Text in Excel

This will open the Notepad.

❻ Now press CTRL + V to paste the copied formulas into the Notepad.

❼ After that, select all the formulas again in Notepad and press CTRL + C to copy them all.

Paste formulas in notepad to Copy a Formula and Paste as Text in Excel


1.3 Use Text Import Wizard

❶ Now create another new column, where you want to paste the formulas as text.

I’ve created a column called Formula as Text in column H.

❷ Now select the top cell of the newly created column.

❸ Then go to Home > Paste > Use Text Import Wizard.

Use text import wizard to Copy a Formula and Paste as Text in Excel

❹ A dialog box will appear. Make sure the option Delimited is checked and hit the Next button.

❺ After that, all the Delimiter options will appear. Uncheck them all and hit the Next button once again.

❻ In the next dialog box, from the Column Data Format section select Text and hit the Finish button.

After following all the steps above, you will see the formulas have been pasted as text in the column called Formula as Text in column H.

Related Content: How to Copy Formula in Excel (6 Quick Methods)


Similar Readings:


2. Use VBA Code to Copy a Formula and Paste It as Text in Excel

To use the VBA Code,

❶ First of all, press the ALT + F11 buttons to open the VBA editor.

❷ Then create a new Module from the Insert tab.

Create a new module to Copy a Formula and Paste It as Text in Excel

❸ Now copy 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

❹ After that paste and save the above code in the VBA code editor.

Use VBA Code to Copy a Formula and Paste It as Text in Excel

Now it’s time to run the code. To do that,

❺ Press ALT + F8 key together. This will open the Macro dialog box.

❻ Just hit on the Run button.

After that, you will see all the formulas in column E has been pasted to the next column of it as in the picture below:

Read More: VBA to Copy Formula from Cell Above in Excel (10 Methods)


Things to Remember

  • Press ALT + F11 to open the VBA code editor.
  • You can press ALT + F8 to open the Macro dialog box.
  • To run the VBA code press the F5 button.

Conclusion

To sum up, we have discussed 2 ways to copy a formula and paste it as text in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo