[Fix] TRIM Function Not Working in Excel: 2 Solutions

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has a myriad of useful Excel functions, one of which is the Excel TRIM function, which makes work easier for users. However, in many circumstances, the TRIM function does not work for a variety of reasons. So, today’s article will assist you in determining what to do if the Excel TRIM not working in the dataset.


How the TRIM Function Works in Excel

In Excel, Trim Function can trim extra leading and trailing spaces found within cells containing text. Normally, The spaces which are given before a word is called Leading spaces.

How the TRIM Function Works in Excel

The spaces which are given end of a word is called Trailing spaces.

How the TRIM Function Works in Excel

The TRIM Function is,

=TRIM(text)

Where text is required argument for the TRIM Function to trim text or number.

From our dataset, we’ll learn how the TRIM function works. Please follow the instructions to learn the working procedure of the TRIM function!

Steps:

  • First, select a cell where we will type the TRIM function, from our dataset we select cell C5 and type the TRIM function. The TRIM function is,
=TRIM(B5)

How the TRIM Function Works in Excel

  • After pressing Enter on your keyboard, we will be able to remove spaces before and after of Am a z o  n, which has been given below screenshot.

  • Now, place your cursor on the Bottom-Right of cell C5 and instantly an autoFill sign will appear in front of you and drag it downward.
  • By dragging the autoFill sign downward, you will be able to trim spaces to the entire column B in column C.

How the TRIM Function Works in Excel


2 Suitable Ways to Fix TRIM Function Not Working in Excel

While importing or copying text data in an Excel spreadsheet, the worksheet occasionally carries unnecessary spaces to the data that you have imported. Ordinarily, the TRIM() function, can extract these unnecessary spaces. TRIM(), further, doesn’t always perform the task. Today, we’ll learn two suitable ways to fix the TRIM function not working in Excel. Here’s an overview of the dataset for our today’s task.

excel TRIM not working


1. Apply the SUBSTITUTE Function to Fix TRIM Function Not Working

We can apply the SUBSTITUTE Function to fix the TRIM function not working in Excel. We have to use the SUBSTITUTE Function in two ways. Let’s follow the steps below to learn.

1.1 Insert the Basic SUBSTITUTE Function

In this sub-method, we’ll learn the basic SUBSTITUTE function. Please follow the instructions to learn!

Steps:

  • First of all, select a cell where we’ll apply the SUBSTITUTE function and we’ll select cell D5 from our dataset to apply the SUBSTITUTE function.

Insert the Basic SUBSTITUTE Function

  • After selecting cell D5, type the SUBSTITUTE function in the Formula Bar. The SUBSTITUTE function in the Formula Bar is,
=SUBSTITUTE(B5, " ", "")

Insert the Basic SUBSTITUTE Function

  • Now, press Enter on your keyboard and you will be able to get Amazon in cell D5 as the return of the SUBSTITUTE function.

  • After that, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us. Now, drag the autoFill sign downward.

Insert the Basic SUBSTITUTE Function

  • While completing the above process, you will be able to replace Spaces with Empty to the entire column D by applying the SUBSTITUTE function, and the TRIM function can not do that which has been given in the screenshot.

Insert the Basic SUBSTITUTE Function


1.2 Use of the SUBSTITUTE and CHAR Functions

We can use the TRIM, SUBSTITUTE, and CHAR functions to remove non-breaking spaces from a line of text. Please follow the steps to learn!

Steps:

  • First, select cell C5 from our dataset to apply the TRIM, SUBSTITUTE, and CHAR functions to remove extra spaces.

Use of the SUBSTITUTE and CHAR Functions

  • Now, type the TRIM, SUBSTITUTE, and CHAR functions in the Formula Bar and Type the SUBSTITUTE, and CHAR functions inside the TRIM function in the function dialog box. The formula is,
=TRIM(SUBSTITUTE(B5,CHAR(160), 0))

Use of the SUBSTITUTE and CHAR Functions

  • After that, press Enter on your keyboard and you will get Am a z o n as the output of these functions.

  • Finally, place your cursor on the Bottom-Right side of cell C5 and an autoFill sign pops us. Now, drag the autoFill sign downward to get your desired output.

Read More: How to Trim Right Characters and Spaces in Excel


2. Perform Find & Select Command to Fix TRIM Function Not Working

In this method, we’ll learn how to fix the TRIM function not working in Excel by using the Find & Select Command. Here, we want to replace Space with Empty from our dataset. Let’s follow the instructions below.

Steps:

  • From your Home tab, go to,

Home → Editing → Find & Select → Replace

Perform Find & Select Command to Fix TRIM Function Not Working

  • After clicking on the Replace option, a Find and Replace window pops up.

  • From the Find and Replace window, Give Space in the Find what box and Replace with box keeps
  • After that, click on the Replace All box.

  • Now, a new dialog box named Microsoft Excel appears in front of you showing with All done. We made 54 replacements.
  • After that press  OK.

Perform Find & Select Command to Fix TRIM Function Not Working

  • After completing the above process, you will be able to replace Space with Empty that TRIM function can not do which has been given in the below screenshot.

Perform Find & Select Command to Fix TRIM Function Not Working


Things to Remember

👉If the Excel TRIM not working, you may run into issues besides non-breaking spaces. You may face those problems particularly if you’re working with HTML-based source material.

👉 The SUBSTITUTE function is useful for changing any ASCII code with any other code.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to fix the TRIM function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles 


<< Go Back to Excel TRIM Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo