Macro to remove blanks in text

[herve]

Hi all,
I have text in cell C11 to C5000.
Do anyone know a macro that will remove the blank in the text?
Ta

 

[Dr. Stephan Kassanke]

Hi all,
I have text in cell C11 to C5000.
Do anyone know a macro that will remove the blank in the text?
Ta

herve,

it would be helpful if you could be more specific - trailing or leading
blanks, blanks in the middle of text, shall the blanks be completely removed
or shall mutiple blanks be replaced by one ....

Stephan

 

[Terry K]

Hi Herve,
How about
Sub Del_spaces()
Range("C11:C5000").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
I think that should do what you are looking for.
Terry

 

[Guest]

I use two - one to remove blank spaces - and another to completely remove any
hanging spaces at the end of cells (useful after a SQL data pull)
================
Sub Trim_all_blanks_in_cells()
'Trims all blank spaces appended to the end of text in cell values
'Useful when importing text which leaves blank characters in the
'cells. For eliminating *any* spaces on the cells, use a find/replace
'as detailed below in 'eliminate_spaces_in_cell_text'

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = Application.Trim(Cell.Value)
Next Cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub




Sub eliminate_spaces_in_cell_text()

'**** Add a selection statement here <Columns("E:E").Select>,
'**** <Range("A1).Select>, etc

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub
================

 

[Gary Keramidas]

here's another way

Sub RemoveSpaces()

Worksheets("Sheet3").Range("c11:c500").Replace _
What:=" ", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True

End Sub