Excel Trim Function Not Working? Try These Fixes

The Trim function in Excel only works when you pass arguments as texts. For example, the function will not work if you pass a cell location as an argument. Similarly, the Trim function only removes leading and trailing spaces. So if you’re trying to remove spaces between words, the function will seem not to work.

With that said, the Trim formula might not work even if you intend to use it correctly. The problems can range from rookie errors even Excel experts may make, such as typing mistakes to corrupt Office program files.

Check Your Formula

The TRIM function will not work if you’ve incorrectly entered your formula. This includes issues in syntax, argument, and function.


Excel TRIM function syntax

To call any function in Excel, you must enter the equal to (=) sign. Ensure you’ve used the symbol before entering TRIM.

After you call the function, use parentheses () to pass your arguments. The TRIM function uses text as its argument, so you must enclose your value in double quotes (“ ”). If you enter your value without double quotes, you’ll encounter the #NAME? Error.


Formula Wizard Excel

Check if you’ve entered the formula correctly. If you’re new to using Excel, take assistance from the formula wizard that appears as you enter a formula.


Trim function format Excel

You can only pass one argument in the TRIM function. Your argument must be a text and not a cell location. Excel will treat every argument, even numeric, as text in the TRIM function.

Show Formula Option Enabled

If the TRIM function is not showing results, you must have the Show Formula option enabled. You can verify if this feature is active from the Formulas tab.

  1. Launch Excel.
  2. Select Formulas from the menubar.
  3. Deselect Show Formulas from the Formula Auditing section.
    Show Formula Option Excel

Repair Office

You could be dealing with corrupt program files that could create issues in the Excel program. When you have missing or corrupt program files, Excel might not have enough resources to follow your command.

You can perform an online repair and see if it solves your issue.

  1. Open Settings (Windows Key + I).
  2. Head to Apps > Apps & features.
    Apps and Features
  3. Scroll down to Microsoft Office and click on the three-dot icon.
  4. Choose Modify.
    Modify Office
  5. Select Online Repair > Repair.
    Repair Office

Use Alternatives

If you intend to use the TRIM to pass a cell location, you can use the SUBSTITUTE function as an alternative. The SUBSTITUTE function replaces a value with another set value.

You can also use the Power Query to remove leading and trailing space from an entire column. Power Query can also act as an alternative to the SUBSTITUTE function.


You can use the SUBSTITUTE function in =SUBSTITUTE(text/cell, value to be replaced, new value) format. 


In the Excel document above, we’ve used the SUBSTITUTE function to replace spaces between text in A2 with nothing. This removes all spaces in the text.

Power Query

Power Query is a powerful tool to transform your values in Excel. You can remove all spaces or only leading and trailing spaces in Power Query.

Convert to Table

Before you use Power Query, be sure to change your value to a table. Select your data and head to the Insert > Table to convert your range to a table.

Remove Leading and Trailing Spaces

  1. Select your table and head to Data.
  2. Locate the Get & Transform section, then select From Table.
    From Table Excel Power Query
  3. Right-click on the column header, then select Transform > Trim.
    Power Query Trim
  4. From the Home tab, click Close & Load.

Remove All Spaces

  1. Select your table from the grid.
  2. Head to Data > From Table.
  3. Hop on to the Add Column tab.
  4. Choose Custom Column.
    Excel Custom Column
  5. In the Custom Column window, give your column a name under the New column name.
  6. Under the Custom column formula, enter Text.Remove(.
    Text Remove Custom Column
  7. Double-click on your column under Available columns.
    Select Column Custom Column Excel
  8. Enter a comma, then insert a space inside double quotes.
  9. Insert closing parentheses. Your formula should be in the =Text.Remove([Column], “ ”) format.
    custom column Excel
  10. Click OK.
  11. Right-click on the old column > Remove.
    Remove Column Power Query
  12. Head to Home > Close & Load.
    Close & Load Excel

Add a Comment

Your email address will not be published. Required fields are marked *