macro to remove space
 

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

macro to remove space
 

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





"Pawan" wrote in message
...
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

macro to remove space
 

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

--


Gary


"Pawan" wrote in message
...
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

macro to remove space
 

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





"Pawan" wrote in message
...
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

macro to remove space
 

try David Mcritchie's <trimallmacro.


"William Benson" wrote in message
...
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





"Pawan" wrote in message
...
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

macro to remove space
 

That worked William
Thank You

"William Benson" wrote:

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





"Pawan" wrote in message
...
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