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:
- Convert formula to text string with Format Cells command
- Convert formula to text strings with modifying formulas
- Convert formula to text string with VBA
If you want to replace formulas with calculated results or value in Excel, please visit: Replace formulas with calculated results or value
Office Tab: Enable Tabbed Editing and Browsing in Office, Just Like Chrome, Firefox, IE 8/9/10. Read more...
Classic Menu for Office: Bring Classic Menus and Toolbars of Office 2003/XP/2000 Back to Office 2007, 2010 and 2013. Read more...
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.
Convert formula to text string with Format Cells command
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.
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.
Convert 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.
Convert 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.
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.
