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:
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
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.
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...