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.
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.
Function
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.
Argument
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.
- Launch Excel.
- Select Formulas from the menubar.
- Deselect Show Formulas from the Formula Auditing section.
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.
- Open Settings (Windows Key + I).
- Head to Apps > Apps & features.
- Scroll down to Microsoft Office and click on the three-dot icon.
- Choose Modify.
- Select Online Repair > Repair.
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.
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.
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
- Select your table and head to Data.
- Locate the Get & Transform section, then select From Table.
- Right-click on the column header, then select Transform > Trim.
- From the Home tab, click Close & Load.
Remove All Spaces
- Select your table from the grid.
- Head to Data > From Table.
- Hop on to the Add Column tab.
- Choose Custom Column.
- In the Custom Column window, give your column a name under the New column name.
- Under the Custom column formula, enter
Text.Remove(. - Double-click on your column under Available columns.
- Enter a comma, then insert a space inside double quotes.
- Insert closing parentheses. Your formula should be in the
=Text.Remove([Column], “ ”) format. - Click OK.
- Right-click on the old column > Remove.
- Head to Home > Close & Load.
