Excel - TRIM function not working

Asked By Peter B on 16-Jul-08 08:37 AM
I am trying to remove trailing spaces from entires in a column.      In
another column I have used the expression of the following format in each of
the cells next to those that I want to change :    =TRIM(C200)    .  This
has made no difference to the length of the entries in the column and the
trailing spacess have not been eliminated.

Can anyone help please


Bernie Deitrick replied on 16-Jul-08 09:10 AM

It is likely that you have other ASCII characters that look like spaces - try this macro first

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
'-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
' - Optionally reenable improperly terminated Change Event macros
Application.DisplayAlerts = True
Application.EnableEvents = True   'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next   'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

MS Excel MVP
Niek Otten replied on 16-Jul-08 09:11 AM
Hi Peter,

Probably they are not "normal" spaces, but non-breaking spaces or other characters that do not print.

Look at the TRIMALL() function by David McRitchie:


Kind regards,

Niek Otten
Microsoft MVP - Excel
Niek Otten replied on 16-Jul-08 09:37 AM
Bernie is right; it is a macro, not a function

Kind regards,

Niek Otten
Microsoft MVP - Excel
Peter B replied on 16-Jul-08 09:40 AM
Bernie & Niek thanks.

I am not conversant with macros, so might need a bit of help on how to apply

This is a sample of the the information in each cell I am dealing with:

Carex pilulifera                                                Pill Sedge

This has been copied from a cell of the original spread sheet.

Both halves are of different lengths and each half consists of more that one
word.  I want to retain the first half only and to leave no trailing spaces
or gaps, but the words must of course be separated. The maximum length of
the first half is 35 characters. To eliminate the second half, I used the
expression RIGHT(A1, 35) to write the list into another sheet of the
workbook.  The reason I wish to do this is import the new sheet as a table
in an Access database, where it is important that the trailing gaps are

Rick Rothstein \(MVP - VB\) replied on 16-Jul-08 09:56 AM
Right-click the tab for the worksheet where your text is located; then
copy/paste the code Bernie posted for you into the code window that
appeared. Now, go back to the worksheet, select the cell(s) with the spaces
you can't remove, press Alt+F8 and select TrimALL from the list that
appears, and then click the Run button.

Bob Phillips replied on 16-Jul-08 10:01 AM
You're both right. it is a function, implemented by means of a macro <bg>



(there is no email, no snail mail, but somewhere should be gmail in my addy)
Bob Phillips replied on 16-Jul-08 10:03 AM
Go into the VBIDE - Alt F11

Goto menu Insert>Module

Paste the code into the window that opens.

Go back to Excel - Alt-F11

Select the cells to be trimmed

Goto menu Tools>Macro>Macros..., select TRIMALL in the list, and hit the Run

Hopefully all will be well thereafter.



(there's no email, no snail mail, but somewhere should be gmail in my addy)
Harlan Grove replied on 17-Jul-08 10:27 PM

If all this does is trim HTML nonbreaking spaces, just use

Peter B replied on 17-Jul-08 02:08 AM
Harlan and other helpers

Thanks for all your ideas.  The original source was an htm file and the
TRIM(SUBSTITUTE.....) function has solved it.  Great.



If all this does is trim HTML nonbreaking spaces, just use