How To Fix Excel TRIM Function Not Working Issue?

Microsoft Excel is having plenty of useful Excel functions to make the work easy for the users out of which Excel TRIM function is one of them. But in many cases is seen that the Excel TRIM function not working, due to various unpleasant reasons.

So today this article will help you out to know what to do when Excel TRIM function doesn’t work.

How Trim Function Works In Excel?

Microsoft Excel TRIM function is an inbuilt function used to remove unwanted spaces between words in a string. The TRIM function returns a text value with leading and trailing spaces removed.

In Excel, this is categorized as a string/text Function and can be used as a worksheet function (WS) and VBA function (VBA) in Excel.

Just like other Excel functions when you copy or import text data into Excel worksheet, the spreadsheet in some cases retain extra spaces added to the inserted content.

Commonly the TRIM function on its own removes the unwanted spaces, between the words or at the beginning or end of the text string. Well in some situations it is found this fails to work.

And in a computer, a space in two words is not categorized as a blank area but as a character. And there is more than one type of space character.

In Web pages, one space character that the TRIM function won’t remove is non-breaking space. So if from the Web pages you have imported or copied data than in this case you might not be able to remove extra spaced with TRIM function if they are created by non-breaking spaces.

So here know what you need to do if the Excel trim not working, but before moving further check out recognize the differences between non-breaking and regular spaces.

To recover lost Excel data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

How To Fix Excel TRIM Not Working Issue?

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 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

So this is trick that you can use to remove spaces when the TRIM function is not working.

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.

excel-trim

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:

Each and 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.

3# Considerations

Well, if the TRIM function won’t work, then you might face problem 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.

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 substitute function. This doesn’t seem to make 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.

FAQ:

What Is The Difference Between Non-breaking vs. Regular Spaces?

As I told above spaces are characters and each character is referenced by ASCII (American Standard Code for Information Interchange) code value.

The ASCII is an international standard for text characters in computer OS that is used for creating one set of codes for 255 different characters and symbols, utilized in computer programs.

And the ASCII code for non-breaking space is 160 and for regular space is 32 and the Excel TRIM function can only remove the spaces that have ASCII code of 32.

Now know what you need to do to remove space when TRIM not working in Excel.

Why Is Trim Not Working In Excel?
  • Basically TRIM function in excel is designed to delete space character. It is represented with a code value of 32 within the 7-bit ASCII character set.

In this Unicode character set, another space character is also available which is known as non-breaking space. It is frequently used in on web pages like a HTML character. This non-breaking space comes with a decimal value of 160, and thus the Trim function won’t work on its own.

  • Chances are also that your data is having some specific non-printing characters along with the code value besides this 160 and 32.

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.



Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.