Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»Excel Trim Function not Working? Try these Fixes

Excel Trim Function not Working? Try these Fixes

Asmi DhakalBy Asmi DhakalJanuary 3, 2023
excel trim function not working

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.

Table of Contents

  • Check Your Formula
    • Syntax
    • Function
    • Argument
  • Show Formula Option Enabled
  • Repair Office
  • Use Alternatives
    • SUBSTITUTE Function
    • Power Query

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

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.

Function

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.

Argument

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.

SUBSTITUTE Function

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

SUBSTITUTE function

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
fix
Asmi Dhakal
  • Twitter
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
usb a vs usb c

USB A Vs USB C – What’s the Difference?

January 31, 2023
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
You may also like
scroll wheel not working

Scroll Wheel Not Working? Try These 6 Fixes

February 5, 2023
how to connect canon printer to laptop

How to Connect Canon Printer to Laptop

February 5, 2023
how to change mouse sensitivity

How to Change Mouse Sensitivity (5 Effective Ways)

February 5, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.