Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove space | Excel Worksheet Functions | |||
Macro to remove space at front and end of a cell | Excel Discussion (Misc queries) | |||
How to remove a space | Excel Discussion (Misc queries) | |||
Function to remove a space from text in cell WITHOUT macro?? | Excel Worksheet Functions | |||
Macro to remove leading space | Excel Programming |