'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix TEXTJOIN and Other Formulas Displaying as Text in Excel
Question details
The user is trying to use text-manipulation formulas such as TEXTJOIN, LEFT, and RIGHT, but the cells display the actual formula strings instead of returning the calculated results.
- Product
- Microsoft Excel
- Device & OS
- Mac (applicable to Windows)
- Scenario
- Entering standard formulas to extract or join text within a worksheet.
- Observed behavior
- The cell displays the formula exactly as typed (e.g., =TEXTJOIN(...)) rather than the computed value, despite showing correctly in the formula dialog box.
Before proceeding, check the formula bar to ensure your formula begins with an equals sign (=) and does not contain an apostrophe (') or a leading space before the equals sign, as this forces Excel to treat the entry as text.
Change Cell Format to General and Re-enter the Formula
If the cell formatting is set to 'Text' prior to typing, Excel will treat any formula as a literal text string. Changing it to 'General' resolves the issue.
This is the most common reason formulas fail to calculate. Keep in mind that merely changing the format from the ribbon will not recalculate the formula automatically; you must force Excel to re-evaluate the cell content.
Click and drag to highlight all the cells where the formulas are currently displaying as plain text.
Navigate to the 'Home' tab on the ribbon. In the 'Number' group, click the dropdown menu (which likely says 'Text') and select 'General'.
Double-click the first affected cell to enter edit mode, or select it and press F2 (or fn + F2 on a Mac keyboard). Press Enter to evaluate the formula.
Turn Off the Show Formulas Feature
Excel includes an auditing feature that displays all formulas in a worksheet instead of their calculated results. Disabling this restores normal behavior.
Set Calculation Options to Automatic
If your workbook is set to manual calculation, formulas will not update or return results when you press Enter.
Use WPS Spreadsheet to Avoid Formula Errors
WPS Spreadsheet offers a highly intuitive interface where text-manipulation formulas like TEXTJOIN, LEFT, and RIGHT work seamlessly. With automated cell formatting checks and full compatibility with Microsoft Excel formats, you can process your data without frustrating display glitches.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Set formatting: Select the target cells, go to the Home tab, and ensure the format is set to 'General'.
- 3. Enter the formula: Type your formula (e.g., =TEXTJOIN(",", TRUE, A1:A5)) into the cell.
- 4. Get instant results: Press Enter to execute the calculation instantly. WPS Spreadsheet defaults to Automatic Calculation for maximum efficiency.
Frequently Asked Questions
Why does my Excel formula still show as text even after I change the format to General?
Changing the cell format to General does not immediately trigger Excel to recalculate the existing entry. You must force Excel to recognize the change by entering edit mode (double-click the cell or press F2) and then hitting Enter.
What is the keyboard shortcut to toggle Show Formulas on and off?
You can quickly turn the Show Formulas feature on or off by pressing 'Ctrl' + '`' (the Grave Accent key, which is usually located above the Tab key on standard keyboards) on both Windows and Mac.
Can leading spaces cause TEXTJOIN formulas to fail?
Yes. If you accidentally type a space before the equals sign (e.g., ' =TEXTJOIN(...)'), Excel will interpret the entire entry as a standard text string instead of a mathematical formula. Ensure the very first character in the cell is the equals sign.
Why am I getting a #VALUE! error when using the TEXTJOIN formula?
A #VALUE! error usually means the formula is successfully evaluating, but there is an issue with the arguments provided. This typically occurs if the text string exceeds Excel's character limits for a cell, or if you are using an older version of Excel that does not support the TEXTJOIN function natively.
