Convert Text That Looks Like a Formula into an Excel Formula
Question details
The user wants to calculate expressions that were entered or stored as text, or intentionally display an expression as formula text.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An entry such as =2*10^2 is expected to calculate, but it may have been stored as text instead of entered as a formula.
- Observed behavior
- Excel does not evaluate a formula when the cell content is stored as text, including entries prefixed with an apostrophe.
Check the formula in the formula bar to confirm whether it begins with an apostrophe or the cell is formatted as Text. Make sure the expression uses valid Excel formula syntax before converting it.
Enter the expression as a real Excel formula
Use this method when you are entering a new expression and want Excel to calculate it immediately.
A formula must begin with an equal sign and must not have an apostrophe before it. For example, Excel calculates =2*10^2 as 200.
Click the cell where you want the calculated result to appear.
On the Home tab, open the Number Format list and select General if the cell is currently formatted as Text.
Type =2*10^2 directly in the cell or formula bar, then press Enter. Do not type an apostrophe before the equal sign.
Convert existing formula text with Text to Columns
Use this method when a range already contains simple formulas stored as text and you want Excel to reinterpret the entries.
Keep an expression visible as text with an apostrophe
Use this method when you want to display a formula example without having Excel calculate it.
Enter and display formula text in WPS Spreadsheet
WPS Spreadsheet lets you enter expressions as working formulas or preserve them as visible text. Use General formatting for formulas you want to calculate, and add an apostrophe when you need to show a formula example without evaluating it.
- 1. Open the worksheet in WPS Spreadsheet: Open your .xlsx or .xls file in WPS Office and select the sheet containing the formula text.
- 2. Choose General formatting: Select the target cell or range, then use Home > Number Format and choose General.
- 3. Enter a formula for calculation: Click a cell, type =2*10^2 without a leading apostrophe, and press Enter to return 200.
- 4. Preserve a formula as displayed text: Type '=2*10^2 when you want the cell to show the expression rather than calculate it.
Frequently Asked Questions
Why does Excel show =2*10^2 instead of returning 200?
The cell is likely formatted as Text or the entry has a leading apostrophe. Change the format through Home > Number Format > General, then re-enter the formula without the apostrophe.
How do I show a formula in a cell without calculating it?
Type an apostrophe before the equal sign, such as '=2*10^2. Excel displays =2*10^2 as text and does not evaluate it.
Can I convert many text formulas at once in Excel?
Select the range, go to Data > Text to Columns, keep the default Delimited option, select Next twice, choose General, and select Finish. Verify the converted cells afterward.
Does changing a cell from Text to General automatically calculate its existing formula text?
Not always. After changing the format to General, edit the cell by pressing F2 and Enter, or use Data > Text to Columns to force Excel to reprocess a selected range.
Can Excel calculate a formula stored in a text string with a worksheet function?
Excel has no standard worksheet function that safely evaluates arbitrary formula text. Advanced desktop Excel workarounds may use a named formula with the legacy EVALUATE function or VBA, but they require careful security and workbook design review.
