Md. Abdur Rahim Rasel
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.
The spaces which are given end of a word is called Trailing spaces.
The TRIM Function is,
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)
- 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.
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.
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.
- After selecting cell D5, type the SUBSTITUTE function in the Formula Bar. The SUBSTITUTE function in the Formula Bar is,
=SUBSTITUTE(B5, " ", "")
- 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.
- 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.
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.
- 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))
- 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
- 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.
- 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.
Read More: How to Trim Part of Text in ExcelÂ
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.
