How to convert text strings to formulas in Excel?
In Excel, you may sometimes encounter situations where you have a large number of text strings that resemble formulas, such as =A1 + B1, but these are stored as plain text rather than functional formulas. Converting each of these text strings into actual formulas manually can be very time-consuming and error-prone, especially if you are dealing with a large dataset. Unfortunately, Excel does not natively provide a direct feature to convert text representations into executable formulas. However, there are several practical methods available that can help address this challenge efficiently and accurately.
By converting these text strings into real formulas, you not only save time but also allow Excel to evaluate and process your calculations as intended. This is particularly valuable when importing data from external sources, automating worksheet setup, or cleaning up files shared from others. In the following sections, you will discover some effective ways to accomplish this task, along with relevant scenarios and important considerations for each approach.
Convert text strings to formulas with a User Defined Function
Convert text strings to formulas with Kutools for Excel
Convert text strings to formulas with a User Defined Function
One common way to convert text strings to formulas in Excel is by using a custom VBA function. This method is especially useful when you need a flexible and reusable solution within your workbook, and when you need to process many text-based formulas quickly.
However, please note that VBA macros need to be enabled to use this solution, and macro-enabled files should be saved as .xlsm to avoid losing your work. Be cautious when sharing macro-enabled workbooks, as some environments might restrict macros due to security policies.
Here’s how you can create and use a User Defined Function (UDF) to convert text strings into active formulas:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. This is the environment where you can write and manage custom VBA code in Excel.
2. In the VBA window, click Insert > Module to create a new module. Then copy and paste the following code into the module window.
VBA code: Convert text strings to formulas
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
Make sure to save your work. If you have not used macros in your workbook before, you may need to enable macro settings.
3. Return to your worksheet. Suppose cell C1 contains the text string (such as =A1+B1) that you want to convert. In another cell where you want to display the result (for example, D1), enter the following formula:
=Eval(C1)
This formula will evaluate the text string as a real formula and return the calculated result.
4. Press Enter to apply the formula. If you want to apply this to multiple rows, select the cell containing the formula (for example D1), and drag the fill handle down to cover the range you need. Excel will recalculate the formula for each corresponding text string. See screenshot:
Some tips and precautions for this method:
- Custom functions like
Evalcreated via VBA will only work in the workbook where the code is present, and will require users to enable macros each time the file is opened. - If the text string references invalid or missing cell addresses, the formula may return an error such as #REF! or #NAME?. Double-check the cell references in your text data before converting.
- Modifying VBA code may impact workbook performance or compatibility with older Excel versions; always back up your file before applying macros.
This solution is best suited for users familiar with or comfortable using VBA, and when you need to automate the conversion and calculation of customized formulas across your worksheet.
Convert text strings to formulas with Kutools for Excel
If you want an even faster method without VBA coding, Kutools for Excel offers a practical Convert Text to Formula feature. This tool lets you convert all selected text strings into real, working formulas in a single step, making it highly efficient for users who frequently handle bulk data transformations.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitations for 30 days. Get it Now.
After you have downloaded and installed Kutools for Excel, follow these steps:
1. Select the cell range containing the text strings you wish to convert.
2. On the Ribbon, go to the Kutools tab, then click Content > Convert Text to Formula.
All the chosen text strings will be instantly converted into active formulas, and the actual calculated values will be displayed in your worksheet. See screenshots:
 |  |  |
This approach is ideal for users who prefer to avoid VBA, work with multiple cells at once, or seek a simple and reliable method. With Kutools, you don’t have to worry about macro settings or writing any code. The conversion is completed in a few clicks, greatly improving productivity and reducing the risk of manual errors.
However, please note:
- The tool only works if the text strings are formatted validly as Excel formulas, starting with '=' and using correct references and operators.
- Formulas with syntax errors will return errors after conversion. If you receive unwanted error values, review the original text for formatting mistakes.
- If you frequently perform this task, consider exploring more batch processing tools offered by Kutools.
To know more about the Convert Text to Formula feature.
Related article:
How to convert formula to text string in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in