Remove space

  • Thread starter Thread starter Gee
  • Start date Start date
G

Gee

I have a large group of prices that I've downloaded from
an internet source. The numbers all have a space, or two,
after them so I can't convert them to actual numbers.
I've tried to find and replace the space and it worked on
the first try, but it left quite a few, now it won't find
any more to replace. How can I get blanks out of the
cells?
Thank you in advance,
Gee
 
Hi Gee

Try this first

Copy a empty cell
Select all your text number cells
Right click and choose pastespecial
Check Add
OK
 
Hi Gee

Select the cells and run this macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
 
That did it!
Thank you so much!
Gee
-----Original Message-----
Hi Gee

Select the cells and run this macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gee" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top