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:
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.
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.
Step 2: Then return to the blank cell C1 and right click it, select the Format Cell from the context menu. See screenshot:
Step 3: In the Format Cells dialog box, click the Text item from the Category box, and then click OK.
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.
Step 5: Repeat from Step 1 to Step 4 to convert other formulas to text strings in the Column C.
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:
Then you need to add the Apostrophe symbol to other cells one by one.
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).
Step 4: Then click the Cell E1, and drag the Fill Handle to the range that you need.
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.
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:
3. And your selected formulas have been converted to text strings at once. See screenshot:
If you want to know more about this feature, please visit Convert Formula to Text.