Forum Discussion
Help! Removing a Space in Excel
If this is in a single column, for example in D1:D12000:
In an empty column, enter the following formula in the cell in row 1:
=TRIM(D1:D12000)
The formula result will spill down to row 12000.
Select the result, copy it, then paste as values over D1:D12000.
You can now clear the column with the formulas.
If the data are in multiple columns, you might use a macro (in the desktop version of Excel).
Let me know if you need that.
TRIM() will remove only leading & trailing spaces but OP wants to remove from middle of sentence. So, TRIM() shouldn't work here.
"TRIM() will remove only leading & trailing spaces but OP wants to remove from middle of sentence. So, TRIM() shouldn't work here."
Not true!
Oh yes! May be TRIM() signature has been updated. Thanks for pointing it out. However, OP has some other character in his string.
We don't know whether the non-breaking space is present in the original data, or it is produced by the forum software (since HTML displays consecutive spaces as one).
Unlike the VBA function Trim, the worksheet function TRIM has always reduced multiple spaces to single ones inside a string.
Anyway, the OP might use
=TRIM(SUBSTITUTE(D1:D12000, CHAR(160), " "))
(substituting the appropriate range, of course)
