macro to remove space

[Guest]

Hi All

I have an excel sheet with lot of text in cells. e.g. "This is a school
". There is space after word school. Same situation is for many cells. I want
to remove this space. Is it possible to write a macro for this?

Thank you in advance.

-Pawan

 

[Mangesh Yadav]

Simply start recording a macro, select the entire sheet, go to find /
replace, and replace "school " with "school" (without the double-quotes
ofcourse). Stop recording the macro. Check the macro code which would be
something like this.


Sub Macro1()
Cells.Select
Selection.Replace What:="school ", Replacement:="school", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


You could also try running the above code.

Mangesh

 

[Gary Keramidas]

is there text after the word with the space after it, or is the space the
last character?

 

[William Benson]

You could use a macro to invoke the TRIM function on each cell, however this
also will turn phrases like

"Bill Benson "
into
"Bill Benson"

which may not be what you want to do.

If you want only to remove any right-most spaces, use the function RTRIM.

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) >= 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub

 

[R.VENKATARAMAN]

try David Mcritchie's <trimall>macro.

You could use a macro to invoke the TRIM function on each cell, however this
also will turn phrases like

"Bill Benson "
into
"Bill Benson"

which may not be what you want to do.

If you want only to remove any right-most spaces, use the function RTRIM.

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) >= 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub

 

[Guest]

That worked William
Thank You

You could use a macro to invoke the TRIM function on each cell, however this
also will turn phrases like

"Bill Benson "
into
"Bill Benson"

which may not be what you want to do.

If you want only to remove any right-most spaces, use the function RTRIM.

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) >= 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub