Re: "It leaves the trailing spaces"
Most likely those spaces are Character 160 (called a non-breaking space).
This formula removes almost all unnecessary characters...
=SUBSTITUTE(CLEAN(TRIM(A2)), CHAR(160), "")
This formula gets just the non-breaking spaces...
=SUBSTITUTE(A2, CHAR(160), "")
The 'Clean Data' utility in my free "Professional_Compare" workbook will do
it for you without having to use a helper column.
Download from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
'---
Nothing Left to Lose
Top answer 1 of 2
127
Re: "It leaves the trailing spaces"
Most likely those spaces are Character 160 (called a non-breaking space).
This formula removes almost all unnecessary characters...
=SUBSTITUTE(CLEAN(TRIM(A2)), CHAR(160), "")
This formula gets just the non-breaking spaces...
=SUBSTITUTE(A2, CHAR(160), "")
The 'Clean Data' utility in my free "Professional_Compare" workbook will do
it for you without having to use a helper column.
Download from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
'---
Nothing Left to Lose
2 of 2
1
Your advice worked perfectly. Thanks. I though it probably had to do with hard spaces, but I did not know how to get rid of them.
Top answer 1 of 4
1
Yep! Character 160 is a "non-breaking space".
See here: How to Remove Non-breaking Spaces in Excel Data - Get Rid of ALL Stubborn Spaces
2 of 4
0
Glad I was able to help!
:)
excel - Trim function and WorksheetFunction.Trim do not remove leading or trailing spaces - Stack Overflow
I copied some data from a website in a worksheet and noticed some cells in addition to the expected value, also contained a trailing space. I wanted to remove these spaces due to causing troubles ... More on stackoverflow.com
Trim Function Not Behaving as Expected
There also are a varying number of spaces after the last character of the field. For this field to be useful to me in the database, I need to remove the trailing spaces. In previous work like this, I have used the TRIM( ) function in Excel to remove the extra spaces. More on answers.microsoft.com
2
58
August 5, 2022excel - Trim Command Not Working - Stack Overflow
Hello Every One, I am trying to trim some data in excel column but leading spaces are not removed and hence vlookup function is also not matching the data properly due to the leading spaces. Can anobody tell me how can I remove these leading and trailing spaces(may be)? More on stackoverflow.com
string - Trim not removing white spaces? - Stack Overflow
I have a field with hex a decimal values that look like FF FE, I need to remove the white space so the results are FFFE but trim is not working for me! Is there another function I can use? More on stackoverflow.com
05:23
Use TRIM with SUBSTITUTE Function in Excel (Remove Extra Spaces) ...
02:45
TRIM Function in Excel | TRIM & CLEAN to Remove Trailing & Leading ...
How To Remove Spaces in Excel (When TRIM Doesn't Work!)
05:12
Simple fix when TRIM function doesn't work (no formula required) ...
07:49
Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?) - YouTube
05:48
Trim function not working? No-break space could be the reason - ...
Reddit
reddit.com › r/excel › remove blank spaces at front and back. trim doesnt work on more than a single space
r/excel on Reddit: Remove blank spaces at front and back. TRIM doesnt work on more than a single space
December 21, 2018 -
I have a sheet of names where there are single space, double space, triple spaces at the beginning of the data. TRIM function only works on single spaces and TEXT TO COLUMN isnt ideal as the spaces vary in different cells. What is the cure.
Edit:Thanks everyone for ur help.
Top answer 1 of 5
10
TRIM trims ALL leading and trailing spaces. Maybe there are different characters in play, html nbsp; for one. This is easy to test in a single cell with a multi space string before and after and then trim it, and do a count of characters with len()
2 of 5
9
what you have might be non-breaking spaces. as u/Excelavator mentioned, TRIM should remove all leading and trailing spaces, and even those more than one in between. and as u/IDELTA86I pointed out, you can try copying that "space" and do a CTRL + H to find a replace. to find out what is that, try: =CODE(LEFT(A1)) it should be 160. but regardless of the result, you can use: =SUBSTITUTE(A1,LEFT(A1),"")
Ablebits
ablebits.com › ablebits blog › excel › excel formulas › excel trim function - quick way to remove extra spaces
Excel TRIM function - quick way to remove extra spaces
March 20, 2023 - See how to use TRIM in Excel to remove extra spaces - leading, trailing and excess spaces between words. Learn why Excel TRIM function cannot remove non-breaking white space and what solution to use.
Excel Forum
excelforum.com › excel-formulas-and-functions › 1408975-cannot-remove-trailing-spaces.html
Cannot remove trailing spaces [SOLVED]
Glen- I wont begin to try understanding your formula but thank you, it worked!! The data was pulled off Xero our accounting software. Its beyond me why they make it SO hard to create a simple overdue statement in CSV format that can be manipulated. Thanks again. Alex ... Hi Spitfire47 Problem is that it is not what it seems, the first character is not a space, but something else char(160). to be solved like this: =VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,",",""),CHAR(160),""),".",","))) cheers
Lifewire
lifewire.com › when-excels-trim-function-doesnt-work-3123658
Use This Alternate Formula When Excel's TRIM Function Doesn't Work
June 15, 2020 - TRIM-SUBSTITUTE-CHAR Formula to Remove Non-breaking Spaces in Excel.
WPS Office
wps.com › blog › how-to-fix-excel-trim-function-not-working
How to Fix Excel TRIM Function Not Working (A Easy Way)
June 1, 2026 - Argument: Ensure you're providing the correct argument (text) to the TRIM function · I found this method to be the most effective way to remove leading and trailing spaces in Excel. However, it can be a bit tricky to get the syntax right.
Ablebits
ablebits.com › ablebits blog › excel › excel formulas › how to remove blank spaces in excel - leading, trailing, non-breaking
How to remove blank spaces in Excel - leading, trailing, non-breaking
March 20, 2023 - There are several ways to remove spaces from string, and this tutorial will help you choose the technique best suited for your particular task and the data type you are working with. If your data set contains superfluous spaces, the Excel TRIM function can help you delete them all in one go - leading, trailing and multiple in-between spaces, except for a single space character between words.
Excel at Work
excelatwork.co.nz › home › excel combine, analyse, report › trim function not working in excel? fix it without formulas
Trim Function Not Working in Excel? Fix It Without Formulas - Excel at Work
August 5, 2025 - By copying and replacing that hidden space, you’ve told Excel to swap out the non-breaking space (which TRIM can’t detect) with a normal space — the kind TRIM can handle. Once the hard spaces are replaced, TRIM kicks in and does what it’s meant to do — removing any leading, trailing, or ...
ExcelDemy
exceldemy.com › home › excel functions › how to fix if trim function not working in excel: 2 solutions
How to Fix If TRIM Function Not Working in Excel: 2 Solutions - ExcelDemy
July 4, 2024 - If the Excel TRIM not Working in the Dataset, We Can Apply the SUBSTITUTE and CHAR functions to Remove Leading and Trailing Spaces
Microsoft Support
support.microsoft.com › en-us › office › trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
TRIM function | Microsoft Support
In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity, ** **. By itself, the TRIM function does not remove this nonbreaking space character.
Atlassian
atlassian.com › data › databases › how to remove leading and trailing spaces in an entire column in excel google docs
Trimming Spaces in Excel & Google Sheets | Atlassian
The trick is to combine TRIM() with a few more functions. We start by using SUBSTITUTE() to replace all instances of CHAR(160) (non-breaking space) with normal space characters. We also use the CLEAN() function which attempts to remove characters ...
DataCamp
datacamp.com › tutorial › how-to-remove-spaces-in-excel
How to Remove Spaces in Excel: 5 Good Methods | DataCamp
March 4, 2026 - Removing trailing spaces. Image by Author. While TRIM() works great for cleaning up spaces in text, you may face issues using it with numeric data. Let me show you what happens: ... The TRIM() function doesn’t remove the spaces correctly. Image by Author. You can see, the TRIM() removed spaces before a number, but Excel doesn't treat the result as a number anymore — it treats the outcome as text.
Stack Overflow
stackoverflow.com › questions › 43442676 › trim-command-not-working
excel - Trim Command Not Working - Stack Overflow
Hey @Slai , no, TRIM doesn't remove spaces between characters, only leading and trailing spaces.
JustAnswer
justanswer.com › computer-programming › klkvj-trim-function-won-t-rid-blank-space-end-text.html
How to Fix Trim Function Issues in Excel and Oracle SQL | JustAnswer
When Excel’s TRIM doesn’t remove trailing spaces, hidden characters like non-breaking spaces (CHAR(160)) may be present. Use the CLEAN function combined with SUBSTITUTE to replace CHAR(160) with regular spaces before trimming.
MyExcelOnline
myexcelonline.com › home › how to remove leading and trailing spaces in excel
How to Remove Leading and Trailing Spaces in Excel
October 3, 2024 - STEP 5: Now that we do not have the non-breaking spaces anymore, let us use the TRIM Formula! =TRIM(SUBSTITUTE(C9, CHAR(160), " ")) Now all of your leading and trailing spaces are cleaned up! You can use the find and replace feature in Excel ...