5 Easy Ways for TRIM Function Not Working in Excel

As we all know, Microsoft Excel has plenty of useful functions to make the work easy for users. The “TRIM” function is one of them. This function helps to remove unwanted spaces between words in a string and returns a text value with leading and trailing spaces removed. But sometimes TRIM function refuses to work due to various unpleasant reasons. In this blog, I will explore the common causes and some quick solutions to fix the TRIM function not working in Excel in no time.

Why Is TRIM Not Working in Excel?

In Excel, TRIM is categorized as a string/text Function and can be used as a worksheet function (WS) and VBA function (VBA) in Excel. However, it fails to work due to various reasons mentioned below:

1. Incorrect syntax or arguments
2. Missing parentheses
3. Corrupted workbook
4. Extra spaces are added to the inserted content.

After identifying the major factors, now it’s time to know how to fix Excel TRIM not working problem with the best solutions.

How to Fix TRIM Function Not Working in Excel Issue?

Let’s take a glance over all the appropriate methods:

Way 1- Remove Entire Spaces Including the Spaces Between Words

If you don’t want any spaces between the words and in a formula you were using TRIM then use SUBSTITUTE.

Here check out how to use SUBSTITUTE formula.

• See the cell A2 in the screenshot,

• Locate the entire ” ” (in the inverted commas detect the gap by clicking space key on the keyboard)
• And replace them with “” (detect no gap, you need to replace it with nothing)
• The instance number will left blank as we need everything to be replaced

Also Read: Microsoft Excel Transpose Not Working? Fix It Now!

Way 2- Remove Non-breaking Spaces

You can remove the non-breaking space from the line of text by making use of TRIM, SUBSTITUTE, and CHAR functions.

As inside the TRIM function, the SUBSTITUTE and CHAR functions are nested. And you can type the formula into the worksheet by using the functions dialog boxes for entering the arguments.

Follow the below-given instruction:

• First, copy line of the below text that contains many non-breaking spaces between the words non-breaking and spaces, into the cell (for example C1)

Removing non-breaking         spaces

• Then click cell C3 (this is the cell where the formula for removing the spaces will be placed)
• And type the below-given formula in the cell C3 and hit Enter key

 =TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

• The text line removing non-breaking spaces in Excel appear in cell C3 without any spaces between words
• And click cell C3 for showing the complete formula> appears in the formula bar above the worksheet.

In this way, you can remove space between the words when the Excel TRIM function won’t works.

How The Formula Works:

Every function performs its particular task:

• The CHAR function is used for entering ASCII codes for two different spaces into the formula – 160 & 32
•  The SUBSTITUTE function replaces entire non-breaking spaces between the words with regular spaces.
• And the TRIM function removes extra regular space between words thus the statement emerges in the worksheet.

Way 3- Considerations

Well, if the Excel TRIM not working, then you might face problems other than non-breaking spaces especially if you are working with original source material offered in HTML.

When you paste material in Excel, try to paste it as plain text for stripping background formatting from string and eliminate special formatting like characters that are provided as white – looks like space but is not.

Also, check for embedded tabs that might be replaced by utilizing same formulas as above but replacing ASCII code 160 with 9.

For replacing any ASCII code with any other SUBSTITUTE is useful.

Now check the trick how to remove space the Excel says there are no spaces.

Also Read: Excel TEXTJOIN Not Working? Discover the Solution Here!

Way 4- Remove Space When Excel Says There Are No Spaces (but you see them!)

Well, sometimes Excel won’t recognize the space but you can see them. Commonly when you do a Find-Replace, Excel ignores it and leaves the spaces in it.

The space you are looking is not space generated by the keyboard; the quick fix is to paste it into the substitute function. This doesn’t seem to make a difference.

Follow the steps to remove spaces from Excel cell.

• In one cell highlight the gap

• Then copy the gap (press CTRL + C)
• Create SUBSTITUTE formula but paste an item into Old Text option (you won’t but Excel recognize a different type of space)

• And Excel will replace the spaces named as spaces with nothing.

Way 5- Repair Damaged Excel Workbook

As I have already mentioned in the causes section, corruption in the specific workbook can also lead to TRIM not working in Excel problems. In such a case, you can use a free Open and Repair tool to overcome this hectic situation.

Steps to be followed to run this feature:

• Launch Excel application >> Go to Open.

• Navigate the folder to choose the corrupted Excel workbook.
• Under Open dialog box, select the problematic XLS/XLSX file.
• After this, click an arrow next to Open button >> Open and Repair.

• Lastly, click on “Repair” button to recover as much of the workbook data.

If the Open and Repair tool doesn’t work due to severe corruption, you can opt for the most versatile Excel Repair Tool. It can easily fix the corrupted Excel document and recover all the data saved within it.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

Related FAQs:

Why TRIM Is Not Removing Extra Spaces in Excel?

The TRIM function does not remove extra spaces or non-printing characters. It only removes white spaces in the Excel spreadsheet.

How Do I TRIM All Spaces in Excel?

To TRIM all spaces in Excel, go to the Formulas tab, and click the Text dropdown menu under the Functions group. Now, select TRIM. As soon as the dialog box appears, click on the cell that contains the text you would like to remove spaces >> click OK.

What Is the Alternative to TRIM In Excel?

You can use the SUBSTITUTE, LEFT, RIGHT, and LEN functions in Excel instead of TRIM to remove spaces and perform complex tasks.

Also Read: Excel Sum Formula Not Working Returns 0: A Step-by-Step Guide

Conclusion:

So this is all about Excel TRIM not working. You can try the given tricks to remove spaces in your Excel file when you import data from Web pages in Excel.

I tried my best to put together how to remove different spaces in Excel when TRIM function won’t work. Apart from that, you can check out our tutorial section to learn advanced Excel for more productive and easy utilization of Excel functions/formulas.