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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.