how to remove spaces in a cell/column

[Guest]

hi
I want to remove spaces in a middle of a cell/column.

regards

P.M.SANJAY

 

[George Nicholson]

Do you need something other than the SUBSTITUTE (and/or REPLACE) functions?

 

[Bernard Liengme]

Give us an example of what is in the cell

 

[Guest]

Here is VBA code from David McRitchie that works for me. Copy starting with
Sub,open your spreadsheet, right click on the tab at the bottom, select View
Code, paste the VBA code, and close out of VBA (file, close and return..).
Highlight the columns that need the spaces removed, go to Tools, Macro,
Macros, Run and select the RemoveAllSpaces, and select Run. That should do
it!!

Good luck!
Carole O

Sub RemoveAllSpaces()
'David McRitchie 2000-10-24
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[wjohnson]

Select your Column or Columns:
Go To EDIT - REPLACE - in the FIND - press the space bar 1 time.
Leave the REPLACE blank.