Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

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

CONVERT FORMULAS TO TEXT STRINGS

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.

convert between text formula 01 convert between text formula 02

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.

convert between text formula 03

3. Click OK or press ENTER.

convert between text formula 03

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

convert between text formula 05 convert between text formula 06


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.

convert between text formula 03

3. Click Values and OK.

convert between text formula 08 convert between text formula 09

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

convert between text formula 03


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.

convert between text formula 08   convert between text formula 09

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.

convert between text formula 03

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.

convert between text formula 03

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.

convert between text formula 03

After the operation, this is how it looks like:

convert between text formula 03


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.

convert between text formula 03

Now you can enjoy the result.

convert between text formula 03

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 (=).

convert between text formula 03

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

convert between text formula 03


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.

convert between text formula 22

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

convert between text formula 23


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.

convert between text formula 24

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

convert between text formula 25


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:

convert between text formula 28

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

convert between text formula 29


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:

convert between text formula 30

This is how it looks after the conversion.

convert between text formula 31

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.

convert between text formula 32

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:

convert between text formula 34

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

convert between text formula 35


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:

convert between text formula 36

This is how it looks after the conversion.

convert between text formula 37

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



  • 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...
kte tab 201905
  • 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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.