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 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.
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:
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:
3. Then click Replace All button, you can see all the calculated results are replaced with the original formula text strings, see screenshot:
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).
4. Then click the Cell D2, 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.
|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:
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:
If you want to know more about this feature, please visit Convert Formula to Text.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoThe find and replace facility is extremely useful, I didn't expect it to work when showing formulae instead of result. Thanks
To post as a guest, your comment is unpublished.· 6 months ago[quote name="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!
Converting the range of data into Table (Insert>Table) may help you.
As and when a row of data is appended to and inserted in to an existing table, formulas and format of number in columns are applied to the new data also.
File size may increase in Table mode. The Table may be changed to Range mode later (Table tools>Tools>convert to range to reduce file size if needed.
Maintaining data in Table mode has advantages.
To post as a guest, your comment is unpublished.· 9 months agoThe Visual Basic editor option didn't work for me - kept returning errors about it needing to be the first row?
To post as a guest, your comment is unpublished.· 1 years agoHi, 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!
To post as a guest, your comment is unpublished.· 6 months agoInserting the “concatenation operator” (& or &” “&) among 230 Excel cells can be done in MS Word. This method is good enough if the task is not repetitive and if a Procedure is not available.
(1) Arrange all the 230 cell-references (the text of which is to be merged in to one cell) in single column or single row in a Table in the merging order (use Excel worksheet and paste in Word).
(2) Select the table and merge cells (Table Tools > Layout > Merge Cells) to get multi-line single-column Table with paragraph mark at the end of each line except last line.
(3) Replaces all paragraph marks with commas (Editing > Replace; Find what = ^p and Replace with = ,).
(4) Convert table to text to get content of each cell separated by a comma in a single paragraph.
(5) Replaces all commas with concatenation-operator or operators, & or &” “& as required (Editing > Replace; Find what = , and Replace with = & or &” “&).
(6) Prefix “=” to the paragraph. Required formula is ready in text form. Copy and paste in Excel; it becomes a formula.
(7) The same result can be achieved in MS Excel also through Editing > Fill > Justify but with limitations. (a) Width of merged text that can be displayed is equal to the width of cell where Justify is applied. Superfluous text is written in cells below as separate text. (b) Numbers are to be converted to text (Formulas > Text > TEXT(Value, Format_text).
I am an Excel user only and I am sorry for my earlier inappropriate suggestion.
To post as a guest, your comment is unpublished.· 1 years agoi 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.
To post as a guest, your comment is unpublished.· 1 years agoHi, 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.