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 as quickly as you can.

doc-convert-formula-text-9-9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!



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


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


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:

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.

Download and free trial Kutools for Excel Now !


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

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 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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

Comments  

Permalink 0 commet
Hello, could you please advise if it is possible to edit the text that is returned from formula? Is it even possible or rather ridiculous? :)
e.g. I have VLOOKUP formula in column B, that returns text string based on value chosen in column A. I need to be able to edit the returned value in column B - have visible the returned text, not the formula, when I click on the cell.
2014-09-24 13:16 Reply Reply with quote Quote
Permalink +3 Andre
A quick way to convert a bunch of cell formulas to text is using the Find/Replace dialogue box.

1. Select the cells containing the formulas.
2. Press Ctrl+H
3. Find what: =
Replace with: '=
4. Replace all

This same trick is also quite useful when you need to work with formulas and not have them change relatively when you're moving them around or transposing them.
2014-10-16 13:49 Reply Reply with quote Quote
Permalink 0 Malcolm
I Replace-All = to '=, works well to show the formulas, Ctrl Z (UNDO) then undoes the change, and repeating CtrlZ CtrlY toggles the change back and forth, making it easy to confirm I notice all the formulas.
My question is, how do I change them back later? When I try to replace '= with = then I get the message "...cannot find any data to replace."
2016-08-26 17:54 Reply Reply with quote Quote
Permalink 0 Malcolm
This is almost perfect, but I suggest using a different character, not the single quote.
Turns out Excel has no simple way to change the '= back to = (kb/124739 explains this surprising fact).
Almost any other character will do, a double quote appears to work, i.e. " , or any string, e.g. XXX=

Also, notice that repeatedly pressing CtrlZ and CtrlY after the replace all will toggle the formulas on and off (so to speak) making it easy to confirm you notice all the formulas. ($0.10)
2016-08-26 19:17 Reply Reply with quote Quote
Permalink 0 César Vega
From Peru, is exactly what I needed, thank you very much
2015-06-02 19:15 Reply Reply with quote Quote
Permalink 0 Hui
thanks ! It helps me a lot ! but i also want to know the inverse one !
if there is a cell (text) '10+20'
I want a cell to show the result 30
I tried Rng.value or Rng.calcultate but don't work ,
what should i do ?
2015-10-03 02:55 Reply Reply with quote Quote
Permalink 0 rjdv
=VALUE(LEFT(A33 ;2))+VALUE(RIGH T(A33;2))
2015-10-09 08:53 Reply Reply with quote Quote
Permalink -1 Andre
Assuming your text function is in A1;

1. Create a text string elsewhere which includes the equals sign eg.
=CONCATENATE("@=",A1)
2. This will give you @=10+20, which you can now copy (paste values) to wherever you want it.
3. Finally, simply replace the @= with = using Ctrl+H as above.

It's three steps, but it avoids VBA and doesn't take long.
2015-10-09 10:43 Reply Reply with quote Quote
Permalink 0 Jim Bradshaw
I am here because I have been trying to resolve a problem with file names. I have some 40 files with identical names except for the year 2 digits.
I simply need to construct the file name (eg C:Docs\Research \...\[Year72.xl sx]Test!$B$10 ro retrieve the data out of that particular file cell B10. I can build the file name in the working workbook,and create the address (using Address), but cannot extract the data in that file cell using Indirect, as Indirect does not access closed files, and opening so many files is not an option. Have we got Microsoft or is there a workaround? as this requirement must be common.
:
2016-03-14 05:53 Reply Reply with quote Quote
Permalink 0 Laura
I need to copy the results from a column with a formula to another column but I want just the results copied not the formula. In other words, I have a column with addresses. I have created another column and using a formula I have removed the house number and now just see the street name. Now I need to copy those results (street names only) to another column in a different spreadsheet, but when I try to do this it only copies the formula which of course then gives me the #ref error because the starting data isn't available.

Any suggestions?
2016-03-19 01:26 Reply Reply with quote Quote
Permalink 0 Ahmed
I need to convert text from value to sort a large data. please help.

For Example

115 gm (this is in shown as text) I need only 115 in next column to sort.


Please reply help in this regards
2016-08-24 12:53 Reply Reply with quote Quote
Permalink 0 César
Quoting Ahmed:
I need to convert text from value to sort a large data. please help.

For Example

115 gm (this is in shown as text) I need only 115 in next column to sort.


Please reply help in this regards


897/5000
Hi, if the numbers inside the text string are in correlative form, try this, in the formula bar type B1 (if the data column is in A1):
= MID (A1, MIN (IFERROR (FIND ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, A1), "")), COUNT (1: $ 98), 1)))
And then press Ctrl + Shift + Enter because it is a matrix formula, ie at the beginning and end of the formula will appear {y}.
Note that I use the comma (,), but it is likely that you must use the semicolon (;) to create both the array of digits and to separate the arguments of the function.
I apologize if there is something wrong with the translation from Spanish, if it gives you an error in case I copy the formula in that language:
= EXTRACT (A1, MIN (IF YOU FIND ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, A1) , FILA ($ 1: $ 98), 1)))
Original by Moisés Ortíz (https://exceltotal.com/extraer-numeros-de-una-celda-en-excel/)
2017-02-02 20:15 Reply Reply with quote Quote
Permalink 0 Ahmed
i need to change my text which in column 115 gm to 115. how it is possible. need to change text in to value like 115 in next column.
2016-08-24 12:55 Reply Reply with quote Quote
Permalink 0 Jennifer
Hi, if you use the text to column feature you can do this. highlight the column in question, click on text to column, step through the process. It will ask you what your delimiter is, if there is a space between the quantity and the measurement (115 gm) you can use the space as your delimiter. PS... add a column to the right of your data column just incase it does something wonky on you.
2017-01-18 23:31 Reply Reply with quote Quote
Permalink 0 Jennifer
Hi, is there a short cut method to concatenate 230 cells in excel to and text string, or do I have to add each cell to the concatenate formula with at divider? Thanks so much!
2017-01-18 23:27 Reply Reply with quote Quote
Permalink 0 Jenny T
The Visual Basic editor option didn't work for me - kept returning errors about it needing to be the first row?
2017-04-07 21:23 Reply Reply with quote Quote

Add comment


Security code
Refresh