公式顯示為文本而不是結果
輸入 =B2*C2 後單元格顯示公式本身，而不是 48。內容被存成了文本，或者工作表只是在顯示公式。如果整張表的公式都露出來，關掉“顯示公式”即可；我這次沒有點選這些選單。下面的結論來自 2026 年 11 月 13 日在 Google Sheets 裡的測試。
本文目錄
快速解答
輸入 =B2*C2 後單元格顯示公式本身，而不是 48。內容被存成了文本，或者工作表只是在顯示公式。如果整張表的公式都露出來，關掉“顯示公式”即可；我這次沒有點選這些選單。下面的結論來自 2026 年 11 月 13 日在 Google Sheets 裡的測試。
=B2*C2檢查重點
在 Google Sheets 裡，真正的公式 =B2*C2 得到 48，ISFORMULA 為 TRUE，ISTEXT 為 FALSE，FORMULATEXT 為 =B2*C2。同樣的字元若按文本儲存，則 ISFORMULA 為 FALSE，ISTEXT 為 TRUE，FORMULATEXT 為 #N/A。前置撇號會隱藏，LEN 為 6；前置普通空格（UNICODE 32）可用 TRIM 去掉；前置 UNICHAR(160) 則 TRIM 去不掉，SUBSTITUTE 可以去掉。清理後的結果仍是文本，需要重新輸入成公式。
邊界與相容性
Excel 裡若單元格事先被設為“文本”格式，通常會把公式存成文字；我這次沒有在 Excel 裡複測。Google Sheets 不同：把單元格設為純文本後再輸入 =B2*C2，仍然得到 48。按文本儲存的公式，把格式改回自動不會開始計算，重新輸入後才得到 48。在 Google Sheets 裡把 = 替換成 = 的三項測試仍保持為文本。我沒有在 Excel 裡執行查詢替換。
完整英文範例、公式與作者備註
Excel Error fixYou type =B2*C2, press Enter, and the cell shows =B2*C2 instead of 48. That happens when the entry is stored as text, or when the sheet is only set to display formulas.
If every formula on the sheet suddenly shows, Show Formulas is on. In Excel that’s on the Formulas tab; in Google Sheets it’s under View > Show > Formulas. Turn it off and the calculated values come back. The setting only changes what’s on screen. I didn’t click those menus for this page. If just one cell or a pasted column is affected while other formulas still calculate, the problem is in the cell, and that’s what I tested below.
The sample: line totals for six products
Type this into A1:C7, and type Line total in D1.
| A | B | C |
|---|---|---|
| Product | Quantity | Unit price |
| Desk lamp | 4 | 12 |
| Mouse pad | 6 | 8 |
| Webcam | 2 | 58 |
| USB hub | 3 | 24 |
| Monitor stand | 1 | 75 |
| Keyboard | 2 | 45 |
In D2, enter:
=B2*C2When D2 is a real formula, it returns 48. The checks on this page used that same cell.
How to tell text from a formula
With Show Formulas off, and with E2:G2 left in a normal format, enter:
=ISFORMULA(D2)
=ISTEXT(D2)
=FORMULATEXT(D2)
I ran those in Google Sheets on 13 November 2026:
| D2 contains | D2 displays | ISFORMULA | ISTEXT | FORMULATEXT |
|---|---|---|---|---|
| Formula stored as text | =B2*C2 | FALSE | TRUE | #N/A |
| Working formula | 48 | TRUE | FALSE | =B2*C2 |
FORMULATEXT and ISFORMULA are in Excel 2013 and later, and in Google Sheets. If Show Formulas is still on, the diagnostic cells will show their own formulas too, so turn it off before you read them.
A leading apostrophe or space
A character before the equals sign stops calculation even in a normally formatted cell.
I entered '=B2*C2 with a leading apostrophe, the way you type a prefix to force text. The cell displayed =B2*C2, ISFORMULA returned FALSE, and FORMULATEXT returned #N/A. The apostrophe itself did not appear in the cell, and LEN was 6, the same as =B2*C2. Open the formula bar, make sure = is the first character, and press Enter. After that, the cell returns 48.
A leading space is easier to miss. I entered =B2*C2 with an ordinary space in front (UNICODE 32, LEN 7). It stayed text. TRIM removed that space, but the TRIM result is still text until you enter the cleaned string as a formula.
Don’t strip spaces that belong inside quotes or elsewhere in the formula. Only the characters before the first = need to go.
Pasted formulas and non-breaking spaces
Copying from a web page, an email, or a CSV often brings a leading space, an apostrophe, or a non-breaking space (UNICHAR(160)). I stored =B2*C2 with a leading UNICHAR(160): UNICODE returned 160, and TRIM left the character in place. If that character is in D2, =SUBSTITUTE(D2,UNICHAR(160),"") returns the text =B2*C2. That cleaned string is still text; you still have to enter it as a formula. The less common leftover characters are in TRIM not working.
Once the formula is calculating, imported quantities can still be text and throw the result off. That’s a separate problem; converting numbers stored as text covers it.
In Excel, a Text format can also be the cause
In Excel, if the cell was already formatted as Text when the formula was entered, Excel stores the characters instead of evaluating them. I did not re-run that in Excel for this page. The usual fix is to change the format to General, then re-enter the formula; changing the format alone isn’t enough.
Google Sheets is different, and I did test this. I formatted a cell as Plain text and typed =B2*C2. It still returned 48, and ISFORMULA was TRUE. Plain text format did not block a typed formula.
What did stay as text in Sheets was a formula stored as literal text, the same characters written without being entered as a formula. Changing that cell from Plain text back to Automatic left it showing =B2*C2. After I re-entered =B2*C2, it returned 48, ISFORMULA was TRUE, and FORMULATEXT returned =B2*C2.
For a single cell, I’d fix the leading character if there is one, change the format if Excel has it set to Text, and re-enter the formula. I also tried replacing = with = on three text formulas in Google Sheets; they stayed text, so I wouldn’t rely on that trick here. I have not run Find & Replace in Excel for this page.
When it starts calculating and shows an error
If after re-entering you get #NAME? instead of 48, the text problem is gone and the formula is being evaluated. A misspelled function name is the usual cause; see the Excel #NAME? error guide. Don’t keep toggling the number format at that point.