## 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****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**

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.

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

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

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!