remove trailing spaces in excel trim not working

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

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

answers.microsoft.com

August 5, 2022

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

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

stackoverflow.com

Videos

05:23

YouTube

Use TRIM with SUBSTITUTE Function in Excel (Remove Extra Spaces) ...

TRIM Function in Excel | TRIM & CLEAN to Remove Trailing & Leading ...

December 7, 2023

youtube.com

How To Remove Spaces in Excel (When TRIM Doesn't Work!)

05:12

YouTube

Simple fix when TRIM function doesn't work (no formula required) ...

October 14, 2020

07:49

YouTube

Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?) - YouTube

Trim function not working? No-break space could be the reason - ...

October 26, 2022

View all

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

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

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.

Find elsewhere

Google Bing Mojeek

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.

Stack Overflow

stackoverflow.com › questions › 55445957 › trim-function-and-worksheetfunction-trim-do-not-remove-leading-or-trailing-space

excel - Trim function and WorksheetFunction.Trim do not remove leading or trailing spaces - Stack Overflow

Top answer

1 of 1

After spending a looooong time searching on the net, I found out here that there is also another character for space, the output of Chr(160) and for some reason Trim functions ignore it. So the solution in my case was firstly replace all these 'different' spaces and then trim values:

Worksheets(2).Range("D17").Value = Replace(Worksheets(2).Range("D17").Value, Chr(160), " ")
Worksheets(2).Range("D17").Value = Trim(Worksheets(2).Range("D17").Value)

In case you encounter a similar problem and run into a different-from-Chr(160) 'unknown' character, you can find out what this character is, by isolating this character and using the Asc function.

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.

Stack Overflow

stackoverflow.com › questions › 22115380 › trim-not-removing-white-spaces

string - Trim not removing white spaces? - Stack Overflow

Top answer

1 of 1

Trim only removes whitespace at the beginning and end. Use substitute instead.

=SUBSTITUTE({cell ref}," ","")

should do the trick

Stack Overflow

stackoverflow.com › questions › 9578397 › remove-leading-or-trailing-spaces-in-an-entire-column-of-data

excel - Remove leading or trailing spaces in an entire column of data - Stack Overflow

Top answer

1 of 6

242

Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

You can also remove the CHAR(160) directly without a workaround formula by

Edit .... Replace your selected data,
in Find What hold ALT and type 0160 using the numeric keypad
Leave Replace With as blank and select Replace All

2 of 6

142

If you would like to use a formula, the TRIM function will do exactly what you're looking for:

+----+------------+---------------------+
|    |     A      |           B         |
+----+------------+---------------------+
| 1  | =TRIM(B1)  |  value to trim here |
+----+------------+---------------------+

So to do the whole column...
1) Insert a column
2) Insert TRIM function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as "Values"

Should be good to go from there...