Excel Convert Formula to Text String: 4 Ways

To convert a formula to a text string in Excel, you replace the formula with its calculated value, or you extract the formula text itself as a string. This article explains four practical methods: copy-and-paste values, using the FORMULATEXT function, applying Find & Replace, and using a simple VBA macro.

Formula

The calculator above works out average pace and speed for a given distance and time. The formulas are:

• Average pace (min/km) = Finish time (minutes) ÷ Distance (km)
• Average speed (km/h) = Distance (km) ÷ (Finish time (minutes) ÷ 60)

Variables:

• t – finish time in minutes
• d – distance in kilometres

The pace tells you how many minutes each kilometre takes; the speed tells you how many kilometres you cover in one hour.

Worked Example

Using the default values from the calculator (t = 50 minutes, d = 10 km):

1. Pace = 50 ÷ 10 = 5.00 min/km
2. Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.00 km/h

The calculator shows 5.00 min/km and 12.00 km/h, matching the steps above.

Why Convert a Formula to Text?

You might want to convert formulas to text strings for several reasons:

• Freeze values – when you share a workbook, you don’t want the numbers to change if someone edits source cells.
• Remove external links – pasting values breaks links to other workbooks.
• Reduce file size – many formulas can slow down large files; static text is lighter.
• Show the formula itself – sometimes you need the formula as a string for documentation or teaching.

The method you choose depends on whether you want the result as text or the formula as text.

Method 1: Copy and Paste Values

The quickest way to turn a formula into its result:

1. Select the cell(s) with formulas.
2. Press Ctrl+C (or right-click and Copy).
3. Right-click the destination cell, choose Paste Special, then select Values.
4. Press Enter.

This replaces the formula with the current calculated value. It works for numbers, dates, and text results. If you want to keep the original cell, paste into a different location.

Method 2: Use the FORMULATEXT Function

If you need the formula as a text string (e.g., to show =A1+B1), use:

=FORMULATEXT(reference)

For example, if cell C1 contains =A1+B1, then =FORMULATEXT(C1) returns the text =A1+B1. This is useful for auditing or documentation. Note that FORMULATEXT only works with formulas in the same workbook; it returns a #N/A error if the referenced cell has no formula.

Method 3: Find & Replace to Remove Equals Sign

To convert all formulas in a range to text (showing the formula, not the result):

1. Select the range.
2. Press Ctrl+H to open Find & Replace.
3. In Find what, type =.
4. In Replace with, type an apostrophe (') or a space.
5. Click Replace All.

This turns formulas into text strings that start with an apostrophe (which hides it in the cell). Be careful: this only works if the formulas are visible as text; it does not work if the cell displays the result. To see formulas, press Ctrl+` (grave accent) to toggle formula view.

Method 4: VBA Macro (for Many Cells)

If you have hundreds of formulas and want to convert them to values automatically, a simple macro works:

1. Press Alt+F11 to open the VBA editor.
2. Insert a new module (Insert > Module).
3. Paste this code:

Sub ConvertToValues()
    Selection.Value = Selection.Value
End Sub

4. Close the editor, select the cells, and run the macro (Alt+F8, choose ConvertToValues, Run).

This replaces each selected cell with its calculated value. It’s a one-way operation – you can’t undo it after closing the workbook.

Common Mistakes and Pitfalls

• Pasting over formulas – if you paste values over the original cells, you lose the formulas permanently. Always paste to a new location if you might need the formulas later.
• FORMULATEXT with external references – it won’t show formulas from other workbooks; you’ll get a #N/A error.
• Find & Replace with formulas – if you don’t toggle formula view first, you’ll replace the equals sign in the displayed result, which may not do what you expect.
• Text numbers – after converting to text, numbers become left-aligned and may not be usable in calculations without converting back.

When to Use Each Method

• Paste Values – best for freezing results permanently.
• FORMULATEXT – best for showing formulas as text in a separate cell.
• Find & Replace – best for converting many formulas to text in one go, but only for display.
• VBA – best for automating the conversion of large ranges without manual steps.

For most users, Paste Values is the simplest and safest. Use FORMULATEXT when you need to document formulas, and use VBA only if you’re comfortable with macros.

Frequently Asked Questions

How do I convert a formula to text without losing the result?

Copy the cell, then paste as values into a different cell. That leaves the original formula intact and gives you a static text version elsewhere.

Why does FORMULATEXT return #N/A?

FORMULATEXT returns #N/A if the referenced cell does not contain a formula, or if the formula is in another workbook that is not open. Check that the reference is correct and that the cell has a formula.

Can I convert a formula to text for the whole column at once?

Yes. Select the entire column (click the column header), then use Paste Values or the Find & Replace method. For Find & Replace, make sure you’re in formula view first.

Does converting to text affect calculations in other cells?

If other cells reference the converted cell, they will still work if the value is numeric text, but they may fail if you convert to a non-numeric string. For example, =A1+1 will error if A1 becomes text like “5â€�.

Is there a way to convert formulas to text in Google Sheets?

Yes, Google Sheets has a similar FORMULATEXT function, and you can copy-paste values the same way. The Find & Replace method also works, but the VBA macro is not available; you’d use Google Apps Script instead.

Related

• Galaxy S7 Volume Not Working: Sound Fix Guide
• New Hires Finish Onboarding but Aren’t Ready? Fix It
• Energy Drink Simulator Codes: Active List & How to Redeem

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2