TRIM function not working

[Peter B]

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

Peter

 

[Bernie Deitrick]

Peter,

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

 

[Niek Otten]

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:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Niek Otten]

Bernie is right; it is a macro, not a function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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:
|
| http://www.mvps.org/dmcritchie/excel/join.htm#trimall
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||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
||
|| Peter
||
||
|
|

 

[Peter B]

Bernie & Niek thanks.

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

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
eliminated.

Peter

 

[Rick Rothstein \(MVP - VB\)]

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.

Rick

 

[Bob Phillips]

You're both right. It's a function, implemented by means of a macro <bg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

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
button

Hopefully all will be well thereafter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

Sub TrimALL() ...
   'Also Treat CHR 0160, as a space (CHR 032)

...

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

=TRIM(SUMSTITUTE(X99,CHAR(160)," "))

 

[Peter B]

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.

Peter

Sub TrimALL() ....
'Also Treat CHR 0160, as a space (CHR 032)

....

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

=TRIM(SUMSTITUTE(X99,CHAR(160)," "))