Delete leading or trailing blank spaces in cell - an example

[DataFreakFromUtah]

No question here, just a couple of procedures for the archive.

Sub DeleteTrailingBlankSpacesOnTheRight()
'Deletes trailing blank spaces on the right of a string
Dim cell As Range
For Each cell In Selection
cell.Value = RTrim(cell.Value)
Next cell
End Sub

Sub DeleteLeadingBlankSpacesOnTheLeft()
'Deletes leading blank spaces on the left of a string
Dim cell As Range
For Each cell In Selection
cell.Value = LTrim(cell.Value)
Next cell
End Sub



Search criteria: delete leading spaces on left
delete spaces on left
delete spaces to the left
delete trailing spaces on right
delete spaces on left
delete spaces to the left
delete blank spaces on left of string
delete blank spaces on right of string
delete blank spaces to left of string
delete blank spaces to right of string

 

[Tom Ogilvy]

Note that this will destroy any formulas in the selection and errors on a
cell containing an error value.

 

[DataFreakFromUtah]

That's a good point Tom. I've made the corrections below.
Cheers,


Sub DeleteTrailingBlankSpacesOnTheRight()
'Deletes trailing blank spaces on the right of a string
Dim cell As Range
Dim rng As Range
On Error Resume Next

Set rng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each cell In Intersect(Selection, rng)

cell.Value = RTrim(cell.Value)

Next cell
End Sub

Sub DeleteLeadingBlankSpacesOnTheLeft()
'Deletes leading blank spaces on the left of a string
Dim cell As Range
Dim rng As Range
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each cell In Intersect(Selection, rng)

cell.Value = LTrim(cell.Value)

Next cell
End Sub

Sub DeleteBlankSpacesOnTheLeftAndRight()
'Deletes blank spaces to the left and right of a string
Dim cell As Range
On Error Resume Next
Dim rng As Range
Set rng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each cell In Intersect(Selection, rng)
cell.Value = Trim(cell.Value)
Next cell
End Sub

Function RemoveTrailingBlankSpacesOnTheRight(TargetCell As Range) As String
'Removes trailing blank spaces on the right of a string
On Error Resume Next
RemoveTrailingBlankSpacesOnTheRight = RTrim(TargetCell.Value)
End Function

Function RemoveLeadingBlankSpacesOnTheLeft(TargetCell As Range) As String
'Removes leading blank spaces on the left of a string
On Error Resume Next
RemoveLeadingBlankSpacesOnTheLeft = LTrim(TargetCell.Value)
End Function


Function RemoveBlankSpacesOnTheLeftAndRight(TargetCell As Range) As String
'Removes blank spaces to the left and right of a string

On Error Resume Next
RemoveBlankSpacesOnTheLeftAndRight = Trim(TargetCell.Value)

End Function