MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Remove spaces at the end of a cell


Posted by Charles on August 09, 2000 3:16 AM

A while ago you were kind enough to help me with a Macro that removed a space (or any character supplied), if it existed, as the *First* character in a cell in a column.

Here it is:

-------------------------------
Sub RemSpace()
'
' RemSpace Macro
'
Dim LastCell As String
Dim CleanWhat
Dim ColRg
Dim cell
Dim TextCells

LastCell = ActiveCell.Address
CleanWhat = Application.InputBox("Enter the text string to clean", "Clean Text", Type:=2)
If CleanWhat = False Then GoTo Exit_Proc

Again:
Set ColRg = Application.InputBox("Select any cell of the Column to check", "Column Selection", Type:=8)
If ColRg Is Nothing Then GoTo Exit_Proc

If ColRg.Columns.Count > 1 Then MsgBox "Select ONE column only!": GoTo Again

Columns(ColRg.Column).Select
Set TextCells = Selection.SpecialCells(xlCellTypeConstants, 2)

For Each cell In TextCells 'Selection
If Left(cell.Text, Len(CleanWhat)) = CleanWhat Then
cell.Value = Mid(cell.Text, Len(CleanWhat) + 1)
End If
Next cell

Range(LastCell).Select

Exit_Proc:
'MsgBox "Done!"
End

End Sub
-------------------------


Now, I want to create a similar macro (from the above) that can remove a space (or any character supplied), if it exists as the *Last* character in a cell.

Can you help please?


Thank you

Charles



Posted by ken on August 09, 0100 3:39 AM

Stupid guess but change left to right

RemSpace Macro


Posted by Celia on August 09, 0100 4:04 AM

RemSpace Macro

Charles
Try changing the For....Next code to :-

For Each cell In TextCells 'Selection
If Right(cell.Text, Len(CleanWhat)) = CleanWhat Then
cell.Value = Left(cell.Text, Len(cell.Text) - Len(CleanWhat))
End If
Next cell

Celia


Posted by Ivan Moala on August 09, 0100 4:08 AM

Charles
change;

If Left(cell.Text, Len(CleanWhat)) = CleanWhat Then
cell.Value = Mid(cell.Text, Len(CleanWhat) + 1)
End If
Next cell


TO

If Right(cell.Text, 1) = CleanWhat Then
cell.Value = Left(cell.Text, Len(cell.Text) - 1)

End If
Next cell

Ivan

Posted by Charles on August 09, 0100 7:51 AM

Thank you

Ivan,

Brilliant, that works a treat!

I am soooo grateful to you.

Thank you soooo much.

With Best Wishes

Charles

Posted by Charles on August 09, 0100 7:53 AM

Thank you

Celia,

Brilliant, that works a treat!

I am soooo grateful to you.

Thank you soooo much.

With Best Wishes

Charles