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 Find and Replace feature

Convert formula to text string with User Defined Function

Convert formula to text string or vice versa with only one click


Convert formula to text strings quickly and easily:

Kutools for Excel's Convert Formula to Text utility can help you convert the formula calculated results to plain text strings.

doc-convert-formula-text-3-3


arrow blue right bubbleConvert formula to text string with Find and Replace feature

Hint


Supposing you have a range of formulas in Column C, and you need to show the column with original formulas but not their calculated results as following screenshots shown:

doc-convert-formula-text-1-1  2 doc-convert-formula-text-2-2

To solve this job, the Find and Replace feature may help you, please do as follows:

1. Select the calculated result cells that you want to convert to text string.

2. Then press Ctrl + H keys together to open the Find and Replace dialog box, in the dialog, under the Replace tab, enter equal = sign into the Find what text box, and enter '= into the Replace with text box, see screenshot:

doc-convert-formula-text-3-3

3. Then click Replace All button, you can see all the calculated results are replaced with the original formula text strings, see screenshot:

doc-convert-formula-text-4-4


arrow blue right bubbleConvert formula to text string with User Defined Function

The following VBA code also can help you easily deal with it.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

3. In a blank cell, such as Cell D2, enter a formula =ShowF(C2).

doc-convert-formula-text-5-5

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

doc-convert-formula-text-6-6


arrow blue right bubbleConvert formula to text string or vice versa with only one click

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 : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as this:( Free Download Kutools for Excel Now! )

1. Select the formulas that you want to convert.

2. Click Kutools > Content > Convert Formula to Text, and your selected formulas have been converted to text strings at once, see screenshot:

doc-convert-formula-text-6-6

Tips: If you want to convert the formula text strings back to the calculated results, please just apply the Convert Text to Formula utility as following screenshot shown:

doc-convert-formula-text-7-7

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

Free Download Kutools for Excel Now!


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

Add comment


Security code
Refresh