PDA

View Full Version : Solved: deleting the space



Sathisc
03-25-2010, 05:31 AM
Hi,

I have full of data with numbers in Column A with space added in the last

1234535628136543213932180

for example in the first number after last digit 5 there is a single space added, same for all

I need a formula to the delete the space as it will be very time consuming if i delete it manually.

Thanks in advance,
Sathish

macropod
03-25-2010, 05:40 AM
Hi Sathish,

Try:

Sub CleanRange()
Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long
With Application
SBar = .DisplayStatusBar
.DisplayStatusBar = True
.ScreenUpdating = False
.Calculation = xlManual
On Error Resume Next
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = .Trim(Replace(Replace(Cell.Value, Chr(160), ""), " ", ""))
I = I + 1
.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Cleaned"
Next Cell
.Calculation = xlAutomatic
.StatusBar = False
.DisplayStatusBar = SBar
.ScreenUpdating = True
End With
MsgBox "Finished cleaning " & CellCount & " cells.", 64
End Sub
If you select a range, the macro acts only on that range; other wise it acts on the whole workbook. A progress report is given on the status bar, plus a message box at the end.

omnibuster
03-25-2010, 05:41 AM
Private Sub TryThis()
Dim c As Range
For Each c In Range("A1:A10")
If Len(c.Value) > 0 Then
c.Value = Val(c.Value)
End If
Next
End Sub

Sathisc
03-25-2010, 05:50 AM
You guys are really great

Thank you so much