'%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)
Fix Excel TRIM Formula Displaying as Text Not Working
Question details
The Excel TRIM function displays the formula text itself instead of executing and removing extra spaces from the targeted cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Trying to clean up text data by removing leading and trailing spaces using the TRIM function.
- Observed behavior
- The cell displays the actual typed formula as ordinary text rather than evaluating it to show the cleaned result.
Ensure that you haven't accidentally typed a single quote (') before the equals sign in your formula, or enabled the 'Show Formulas' view, as both will force Excel to display the formula as text regardless of cell formatting.
Change Cell Format to General and Re-enter Formula
This is the most common and effective fix. When cells are formatted as 'Text', Excel ignores formula calculations. Switching the format to 'General' allows Excel to calculate the formula properly.
If a cell's formatting was set to Text before you typed the formula, Excel will interpret your keystrokes literally. You must change the format back to General and force Excel to recalculate the cell.
Highlight the specific cells where your TRIM formula is displaying as text instead of functioning correctly.
Go to the 'Home' tab on the top ribbon, locate the 'Number' group, click the format dropdown menu (which likely says 'Text'), and select 'General'.
Double-click the cell with the formula (or click directly into the formula bar at the top) and press 'Enter'. This forces Excel to re-evaluate the cell as a formula.
Test the Formula in a New Workbook
If changing the format doesn't work, the issue might be specific to the original document's settings or corruption. Testing in a fresh file isolates the problem.
Easily Clean Up Text Data with WPS Spreadsheet
WPS Spreadsheet provides a seamless and highly compatible environment for your data cleaning tasks. It perfectly handles standard Excel formulas like TRIM, and its intuitive interface makes cell formatting and formula evaluation straightforward.
- 1. Open your document: Launch WPS Spreadsheet and open the file containing the text data you need to clean.
- 2. Prepare an adjacent column: Select the empty cells next to your data and ensure their format is set to 'General' in the Home tab.
- 3. Apply the TRIM formula: Type =TRIM(cell_reference) in the first empty cell and press Enter to remove extra spaces.
- 4. Drag to fill: Click and drag the small square at the bottom-right corner of the cell to apply the TRIM formula to the rest of your list.
Frequently Asked Questions
Why does Excel show my formula instead of the calculated answer?
This usually happens for two reasons: either the cell is formatted as 'Text' before you typed the formula, or the 'Show Formulas' feature is accidentally turned on. You can toggle 'Show Formulas' off by pressing 'Ctrl + `' (grave accent) on your keyboard or clicking it under the Formulas tab.
Can the TRIM function remove non-breaking spaces in Excel?
No, the standard TRIM function only removes regular space characters. To remove non-breaking spaces (often found when copying data from web pages), you need to combine the TRIM and SUBSTITUTE functions. For example, use =TRIM(SUBSTITUTE(A1, CHAR(160), " ")).
How do I quickly apply the General format to an entire column?
Click the column letter at the very top of the worksheet to select the entire column. Next, go to the Home tab on the ribbon, click the Number Format dropdown box, and select 'General'. Remember that you will still need to re-enter or refresh any existing formulas for the format change to take effect.
