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

Recommended Productivity Software

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

doc-convert-formula-text-1


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

Hint


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.


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
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.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase