How to convert between formula and text/value

The tutorial demonstrates different ways to convert a formula to its result or its text string, and to convert a formula in the form of text to a working formula which would show its result in Excel.

CONVERT FORMULAS TO ACTUAL VALUES

• Press F2 + F9 to convert formulas to actual values
• Use Excel Paste Special to convert formulas to actual values
  • Press Alt + E + S + V (Excel Paste Special)
  • Use right-click menu (Excel Paste Special)
• Right-click drag-and-drop trick to convert formulas to actual values
• VBA script (macro) to convert formulas to actual values
• Just one click to convert formulas to actual values

CONVERT FORMULAS TO TEXT STRINGS

• Add an apostrophe (') to convert formulas to text strings
• Replacing trick (Ctrl + H) to convert formulas to text strings
• Show Formulas feature of Excel to convert formulas to text strings
• VBA script (macro) to convert formulas to text strings
• Just two clicks to convert formulas to text strings

CONVERT TEXT TO FORMULAS

• VBA script (macro) to convert text to formulas
• Just two clicks to convert text to formulas

You may find different situations when you want to convert formulas to text or values in Excel:

You need to copy the actual values from the Excel file to another file……
You need to send others an Excel file, but some functions are volatile, the value will recalculate when the file was opened in a different computer with different versions of Excel……
You don’t want the Excel file receiver to know what formulas did you use, for that were the result of your hard work; or your company charges customers money for making them reports with complicated formulas. So you need to replace the formulas with their results……

Don’t worry, here our ExtendOffice team has listed several ways to convert formulas to text or values in Excel to meet the needs of different groups.

---

CONVERT FORMULAS TO ACTUAL VALUES

The methods listed below would help you to convert formulas to their values while deleting the formulas at the same time. Press Ctrl + Z to undo if you want the formulas back.

Press F2 + F9 to convert formulas to actual values

This works for only one cell, but is super easy to use and remember. It’s suitable for the groups that only need to edit one or just few cells. The specific steps are as follows:

1. Move your mouse over the cell and press F2, or double click on the cell, to edit it.

2. Press F9, and then ENTER.

---

Use Excel Paste Special to convert formulas to actual values

When you copy or cut and paste in Excel, everything in the source cell(s) will be pasted to the destination cell(s) by default. But sometimes this might not be what you want. The Paste Special feature in Excel enables you to paste only the things you want. To access the feature, here we listed 2 ways:

• Press Alt + E + S + V (Excel Paste Special)

1. Copy the source cell or range that you want to convert to values.

2. HOLD Alt, and press E, S and V in order.

3. Click OK or press ENTER.

• Use right-click menu (Excel Paste Special)

If you worry that you cannot remember the shortcut above, please follow the following steps:

1. Copy the source cell or range that you want to convert to values.

2. Right click on the destination cell(s), select the icon (Values) below.

Or follow the steps below for more details:

1. Copy the source cell or range that you want to convert to values.

2. Right click on the destination cell(s), select Paste Special.

3. Click Values and OK.

√ A small tip: If there are green triangles in the upper-left corners of the destination cells, select the cell range, the Trace Error button (the yellow icon with an exclamation mark) will appear. Click it, select Convert to Number. The green triangles will then disappear.

---

Right-click drag-and-drop trick to convert formulas to actual values

If you don’t like Paste Special much, you can convert formulas to values this way:

1. Select the source cell or cell range.

2. Position the cursor on the border of the cell(s), now the pointer will change to a move pointer.

3. Right click (don’t let go) with the move pointer, drag the cell to the location you want, and drop (you can also drop on the original location).

4. Click Copy Here as Values Only.

---

VBA script (macro) to convert formulas to actual values

If you are used to using VBA (Visual Basic for Applications) in Excel, here we also have a macro code for you, please do as follows: (Please note that after you run the macro code listed below, you cannot undo the action.)

1. Select the source cell or cell range with formulas that you want to convert to values.

2. Press Alt + F11, the Microsoft Visual Basic for Applications window will show up.

3. Click Insert, and then click Module on the drop-down list.

4. Paste the following code in the Module window.

Sub ConvertFormulasToValues()
'Update by Extendoffice
    Dim xRg As Range
    Dim xCell As Range
    Dim xRet As VbMsgBoxResult
    Dim SLocation As String
    On Error Resume Next
    xRet = MsgBox("You can't undo this operation. " & "Click Yes to copy the workbook automatically?", vbYesNoCancel, "Kutools for Excel")
    Select Case xRet
        Case vbYes
            'Backup a copy of the workbook to the same location.
            ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\Backup" & " " & ActiveWorkbook.Name
            SLocation = ActiveWorkbook.Path & "\Backup" & " " & ActiveWorkbook.Name
            MsgBox "The backup is: " & SLocation, vbInformation, "Kutools for Excel"
        Case vbCancel
            Exit Sub
    End Select
    'Please select the range which you want to convert formulas to values.
    Set xRg = Selection
    If TypeName(xRg) = "Range" Then
        For Each xCell In xRg
            If xCell.HasFormula Then
                xCell.Formula = xCell.Value
            End If
        Next
    End If
End Sub

5. Press F5 to run the code. If you need, click Yes to convert the formulas to their results while copying the workbook automatically, the dialog box will then tell you where the backup is. Click No to operate the conversion directly. Remember, you can’t undo this operation.

After the operation, this is how it looks like:

---

Just one click to convert formulas to actual values

For those who do not like following complicated steps, Kutools for Excel is a very handy tool for you to convert the formulas to values and delete the formulas at the same time in Excel. It literally takes you only one click if you have Kutools installed:

1. Select the source cell range, find the Ranges & Cells group in the tab Kutools.

2. Click To Actual.

Now you can enjoy the result.

Click to download Kutools for Excel for a 30-day free trial.

---

CONVERT FORMULA TO TEXT STRING

The methods listed below would help you to convert formulas to text, so that the text string of formulas would show up instead of their results.

Add an apostrophe (') to convert formulas to text strings

An apostrophe prevents Excel from interpreting the text string as a formula. So adding an apostrophe is always helpful when you want to display the text string instead of the result of a formula (text string). And it’s quite easy, the steps are as follows:

1. Select a formula cell and press F2, or double click on the cell, to edit it.

2. Add an apostrophe (') before the equal sign (=).

3. Press ENTER, you will now see the text string showing in the cell.

---

Replacing trick (Ctrl + H) to convert formulas to text strings

However, in the case you want to display all the text strings of formulas in the current worksheet, the Replace feature in Excel is a better choice for you to solve the problem.

1. Press Ctrl + H, you will see the Find and Replace window.

2. Type = in the Find what box, type ‘= in the Replace with box.

3. Click Replace All. You will see all the formulas showed up in the current worksheet.

---

Show Formulas feature of Excel to convert formulas to text strings

In Excel, there is a feature called Show Formulas hidden in the tab Formulas. By using the feature, the results of formulas would convert to the text strings of formulas easily:

Go to the tab Formulas, find Formula Auditing group, click Show Formulas.

The feature will make all the formulas in the worksheet visible, like this:

---

VBA script (macro) to convert formulas to text strings

If you prefer to use macro codes, the one listed below would help you. Please follow the following steps:

1. Press Alt + F11, the Microsoft Visual Basic for Applications window will show up.

2. Click Insert, and then click Module on the drop-down list.

3. Paste the following code in the Module window.

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

4. Go back to the worksheet, enter the formula =ShowF(source cell) on the destination cell, for example:

5. Press ENTER. You will now see the formula showed up.

---

Just two clicks to convert formulas to text strings

In the case you don’t want to delete formulas but need to convert between formulas and text strings easily, Kutools for Excel is a perfect tool in Excel to help you:

Click Content and select Convert Formula to Text after selecting the cell range that you want to convert, like this:

This is how it looks after the conversion.

Click to download Kutools for Excel for a 30-day free trial.

---

CONVERT TEXT TO FORMULAS

Let’s say you have a report to show both formulas and values, or someone sent you an Excel file showing formulas instead of their results.

Here are 2 ways for you to convert text strings to formulas to show their results directly.

VBA script (macro) to convert text to formulas

1. Press Alt + F11, the Microsoft Visual Basic for Applications window will show up.

2. Click Insert, and then click Module on the drop-down list.

3. Paste the following code in the Module window.

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

4. Go back to the worksheet, enter the formula =Eval(source cell) on the destination cell, for example:

5. Press ENTER. Now you can see the result of the formula.

---

Just two clicks to convert text to formulas

It takes two clicks if you have Kutools for Excel installed in Excel to convert text to formulas: Under the Kutools tab, you can just click Content and select Convert Text to Formula after selecting the cell range that you want to convert, like this:

This is how it looks after the conversion.

Click to download Kutools for Excel for a 30-day free trial.