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 as =CONCATENATE("000", " - 2"), how will you deal with it? There are several ways to solve this problem:

Convert formula to text string with Format Cells command

Convert formula to text strings with modifying formulas

Convert formula to text string with VBA

Convert formula to text string with Kutools for Excel

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.

doc-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: Double click the cell C1,select the formula string and then cut the original formula of =A1+B1 by clicking Ctrl + X keys.

doc-convert-formula-text-2

Step 2: Then return to the blank cell C1 and right click it, select the Format Cell from the context menu. See screenshot:

doc-convert-formula-text-2

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

doc-convert-formula-text-2

Step 4: Then press Ctrl + V to paste the cut formula of =A1+B1 in the cell C1, then you will get the formula showing in the cell.

doc-convert-formula-text-5png

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


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. See screenshot:

doc-convert-formula-text-7-2doc-convert-formula-text-8

Then you need to add the Apostrophe symbol to other cells one by one.


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.

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).

doc-convert-formula-text-9

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

doc-convert-formula-text-10


arrow blue right bubbleConvert formula to text string with Kutools for Excel

If you have Kutools for Excel, with its Convert Formula to Text function, you can change multiple formulas to text strings with only one click.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days.Get it Now.

After installing Kutools for Excel, please do as this:

1. Select the formulas that you want to convert.

2. Click Kutools > Content Convert > Convert Formula to Text, see screenshot:

doc-convert-formula-text-11

3. And your selected formulas have been converted to text strings at once. See screenshot:

doc-convert-formula-text-11

If you want to know more about this feature, please visit Convert Formula to Text.


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