How to convert formula to text string in Excel?

Normally Microsoft Excel will show the calculated results when you enter formulas in cells. However, sometimes you may need to show only the formula in cell, such a =CONCATENATE("000", " - 2"), how will you deal with it? There are several ways to solve this problem:

If you want to replace formulas with calculated results or value in Excel, please visit: Replace formulas with calculated results or value

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Supposing you have a table as following screen shot shows, and you need to show the Column C with original formulas but not their calculated results.

shot-convert-formula-text-1


arrow blue right bubbleConvert formula to text string with Format Cells command

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

You can show the formula as text strings with the Format cells command with following steps:

Step 1: Click the Cell C1, and cut the original formula of =A1+B1.

Step 2: Right Click the Cell C1, and click the Format Cell from the context menu.

Step 3: In the Format Cells dialog box, click the Text item from the Category box, and click OK.

Step 4: Paste the cut formula of =A1+B1 in the Cell C1, then you will get the formula showing in the cell.

shot-convert-formula-text-2

Step 5: Repeat from Step 1 to Step 4 to convert other formulas to text strings in the Column C.

There are also tricky ways to Replace formulas with calculated results or value.


arrow blue right bubbleConvert formula to text strings with modifying formulas

Is it tedious to covert formulas to text string with the method above? There is an easy way. Select and edit one cell, say Cell C1, add an Apostrophe (') before original formula, and press Enter key, it will display the formula =A1+B1 in Cell C1.
There are also tricky ways to Replace formulas with calculated results or value.


arrow blue right bubbleConvert formula to text string with VBA

Both methods above can only deal with single cell, and you can't drag the fill handle across ranges. The following VBA will help you easily deal with it.

Step 1: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert >> Module, and paste the following macro in the Module Window.

VBA for Showing Formula in Cell

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

Step 3: In a blank cell, such as Cell E1, enter a formula =ShowF(C1). Then it shows =A1+B1 in Cell E1.

Step 4: Then click the Cell E1, and drag the Fill Handle to the range that you need.

shot-convert-formula-text-3

There are also tricky ways to Replace formulas with calculated results or value.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh