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 evenExcel expertsmay make, such as typing mistakes to corrupt Office program files.
Check Your Formula
TheTRIMfunction 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 theequal to(=) sign. Ensure you’ve used the symbol before entering TRIM.
After you call the function, useparentheses ()to pass your arguments. The TRIM function uses text as its argument, so you must enclose your value indouble 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 theShow Formulaoption enabled. you may verify if this feature is active from theFormulastab.
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’re able to perform an online repair and see if it solves your issue.
Use Alternatives
If you intend to use the TRIM to pass a cell location, you can use theSUBSTITUTEfunction as an alternative. The SUBSTITUTE function replaces a value with another set value.
You can also use thePower Queryto remove leading and trailing space from an entire column. Power Query can also act as an alternative to theSUBSTITUTEfunction.
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 theInsert > Tableto convert your range to a table.
