Removing non-breaking trailing spaces

[Joanne Bullock]

Hi,

I have a cell in excel which has a not-breaking space at the end. I
need to get rid of it but can't seem to check if the space is a space.
Ive tried RTrim(string), instr(lengthofstring, 1, " ") and if string
= chr(32) but nothing seems to work. Is there anything else I can
try?

Thanks,

Jo

 

[Jonathan West]

Hi Joanne,

This group deals with VBA in Word. The Excel programming experts can be
found at microsoft.public.excel.programming. You will have a much better
chance of an exprrt response if you post your question there.

 

[Helmut Weber]

Hi Joanne,
as Jonathan said, the experts are found in the excel-groups.
Nevertheless, e.g.
for the first cell in the first worksheet in the active workbook
Sub test()
With ActiveWorkbook.Worksheets(1).Cells(1, 1)
If Asc(Right(.Value, 1)) = 160 Then
.Value = Left(.Value, Len(.Value) - 1)
End If
End With
End Sub
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[macropod]

Or, more thoroughly:

Sub TrimRange()
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32)
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Cell.Value) ' Delete "Application." to
maintain any internal double-spacing
Next Cell
MsgBox "Finished trimming " & vbCrLf & "excess spaces", 64
End Sub

You can also trim such cells with a formula. For example, if the problem
cell is in A1:
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

Cheers