Excel Trim Function Not Working? Try These Fixes

The Trim operate in Excel solely works whenever you go arguments as texts. For instance, the operate is not going to work should you go a cell location as an argument. Similarly, the Trim operate solely removes main and trailing areas. So should you’re attempting to take away areas between phrases, the operate will appear to not work.

With that stated, the Trim method won’t work even should you intend to make use of it accurately. The issues can vary from rookie errors even Excel specialists might make, similar to typing errors to deprave Office program recordsdata.

Check Your Formula

The TRIM operate is not going to work should you’ve incorrectly entered your method. This consists of points in syntax, argument, and performance.

Syntax

To name any operate in Excel, you have to enter the equal to (=) signal. Ensure you’ve used the image earlier than coming into TRIM.

After you name the operate, use parentheses () to go your arguments. The TRIM operate makes use of textual content as its argument, so you have to enclose your worth in double quotes (“ ”). If you enter your worth with out double quotes, you’ll encounter the #NAME? Error.

Function

Check should you’ve entered the method accurately. If you’re new to using Excel, take help from the method wizard that seems as you enter a method.

Argument

You can solely go one argument within the TRIM operate. Your argument should be a textual content and never a cell location. Excel will deal with each argument, even numeric, as textual content within the TRIM operate.

Show Formula Option Enabled

If the TRIM operate isn’t displaying outcomes, you have to have the Show Formula possibility enabled. You can confirm if this characteristic is energetic from the Formulas tab.

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

Repair Office

You could possibly be coping with corrupt program recordsdata that might create points within the Excel program. When you’ve lacking or corrupt program recordsdata, Excel won’t have sufficient sources to observe your command.

You can carry out a web based restore and see if it solves your problem.

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

Use Alternatives

If you propose to make use of the TRIM to go a cell location, you should utilize the SUBSTITUTE operate as a substitute. The SUBSTITUTE operate replaces a worth with one other set worth.

You also can use the Power Query to take away main and trailing house from a whole column. Power Query also can act as a substitute for the SUBSTITUTE operate.

SUBSTITUTE Function

You can use the SUBSTITUTE operate in =SUBSTITUTE(textual content/cell, worth to get replaced, new worth) format. 

In the Excel doc above, we’ve used the SUBSTITUTE operate to exchange areas between textual content in A2 with nothing. This removes all areas within the textual content.

Power Query

Power Query is a robust software to remodel your values in Excel. You can take away all areas or solely main and trailing areas in Power Query.

Before you utilize Power Query, remember to change your worth to a desk. Select your information and head to the Insert > Table to transform your vary to a desk.

Remove Leading and Trailing Spaces

1. Select your desk and head to Data.
2. Locate the Get & Transform part, then choose From Table.
3. Right-click on the column header, then choose Transform > Trim.
4. From the Home tab, click on Close & Load.

Remove All Spaces

1. Select your desk from the grid.
2. Head to Data > From Table.
3. Hop on to the Add Column tab.
4. Choose Custom Column.
5. In the Custom Column window, give your column a reputation beneath the New column title.
6. Under the Custom column method, enter Text.Remove(.
7. Double-click in your column beneath Available columns.
8. Enter a comma, then insert an area inside double quotes.
9. Insert closing parentheses. Your method must be within the =Text.Remove([Column], “ ”) format.
10. Click OK.
11. Right-click on the outdated column > Remove.
12. Head to Home > Close & Load.